Geek asks and answers

Condition with CASE in SQL

According to the answer of GORDONS, I executed a request to get the following output.

ROW_ID      ARTIKEL      SUPPLIERID      ORGID      PIECES      COSTPRICE      DISCOUNT    VALUE_DRILL_DOWN
1           TV            SONY          922         6            110           2.5         14
2           TV            SONY          922         10           80            1            4
3           TV            SONY          922         6            65            1.5          0
4           TV            SONY          922         14           95            1.5          0
5           TV            SONY          922         18           95            1.5          0
6           TV            SONY          922         4            95            1.5          0

My request is as follows:

SELECT "SUPPLIERID","ARTIKEL",(case when @x - "cumlativesum") < 0 then NULL else (@x - "cumlativesum") end) as "VALUE_DRILL_DOWN"
from 
(SELECT T1."ARTIKEL",T1."SUPPLIERID",(select sum("PIECES") from EXAMPLE_TABLE T2    where T2."ROW_ID" <= T1."ROW_ID"and T2."SUPPLIERID" = T1."SUPPLIERID" and T2."ARTIKEL"=T1."ARTIKEL") as "cumlativesum" from :EXAMPLE_TABLE T1)

But I want a conclusion like this:

ROW_ID      ARTIKEL      SUPPLIERID      ORGID      PIECES      COSTPRICE      DISCOUNT    VALUE_DRILL_DOWN
1           TV            SONY          922         6            110           2.5         14
2           TV            SONY          922         10           80            1           4
3           TV            SONY          922         6           65            1.5          0
4           TV            SONY          922         14           95            1.5         Null
5           TV            SONY          922         18           95            1.5         Null
6           TV            SONY          922         4            95            1.5         Null

I want the lines involved in computing ie from lines 1 to 3 to have only values, and all the lines that do not take part in the calculation are NULL. As we see in the third row, this value (4-6 = -2). Although the value is negative, this string is part of the value " 20". SO value is set to 0instead of -2or NULL. If I change the condition to (case when @x - "cumlativesum") < 0 then NULL, then all rows where the value is out of bounds 0are set to NULL.

Any suggestions would be appreciated.

+3
1

, , , / 0 . , , . , :

SELECT "SUPPLIERID", "ARTIKEL",
       (case when @x - cumulativesum <= 0 and @x - (cumulativesum -BZBAS_AW) > 0
             then 0
             when @x - "cumulativesum" <= 0
             then NULL
             else @x - "cumulativesum"
        end) as "VALUE_DRILL_DOWN"
from (SELECT T1."ARTIKEL", T1."SUPPLIERID", T1.BZBAS_AW
             (select sum("BZBAS_AW")
              from EXAMPLE_TABLE T2
              where T2."ROW_TEST" <= T1."ROW_TEST" and T2."LIFNR" = T1."LIFNR" and T2."ARTIKEL"=T1."ARTIKEL"
             ) as "cumulativesum"
     from EXAMPLE_TABLE T1
     ) t
+2

More articles:


All Articles