Filter expressions in SQL query

Question

Filter expressions in SQL query

Navigation

#1 by (0 votes)

1

I am trying to perform a query in SQL Developer (Oracle) on several tables. In which I need to compare the result of expression if it is Greater or Equal (x> = 0) or if it is Minor:

X >= 0 => Resultado: 'S' ; X < 0 => Resultado: 'N'

And I also need to put a range of values on an expression that is:

x > 100 => Resultado: 100 ; x < 0 => Resultado 0 ; 0 =< x =< 100 => Resultado: X

I tried to do it through CASE Expressions but I have not received the expected result The query in question is the following:

    SELECT cns.DIA,
SUM(vs.NETO),
    SUM(cns.GASTO),
    SUM(t.ASIG),
        SUM(cns.GASTO)-(SUM(t.ASIG)/SUM(vs.NETO) AS X,
        CASE SUM(cns.GASTO)-(SUM(t.ASIG) + SUM(vs.NETO)) WHEN 0 THEN 'S'
        ELSE 'N' END AS USO
    FROM T1.CONSUMO cns,
         T2.VOLSERV vs,
         T3.TRANS t;

RESULT OBTAINED

DIA         NETO       GASTO    ASIG   USO
19/08/2017  70000      115080   50000   N

I would have to receive an 'S' since it spent more than the account.

Greetings .-

sql oracle

asked by byte96 22.03.2018 в 18:03

source

1 answer

create empty JDialog when changing the event How can I parse more objects that I have within arrays in JSON and kotln?

score 0 · Answer 1

I guess what you want to do is the following:

SQL> select case when 1 >= 0 then 'S' else 'N' end from dual;

C
-
S

Then you would have to change your SQL:

SELECT cns.DIA,
SUM(vs.NETO),
    SUM(cns.GASTO),
    SUM(t.ASIG),
        SUM(cns.GASTO)-(SUM(t.ASIG)/SUM(vs.NETO) AS X,
        CASE WHEN SUM(cns.GASTO)-(SUM(t.ASIG) + SUM(vs.NETO)) => 0 THEN 'S'
        ELSE 'N' END AS USO
    FROM T1.CONSUMO cns,
         T2.VOLSERV vs,
         T3.TRANS t;

Greetings