select issue

deeparavi · #1 (**permalink**) 01-14-11, 06:19

This is my query:
Select
y.num_code,
y.group_code
u.product_code,
y.symbol,
case

when sum (case when y.amount_d_c = 'D' then -y.amount else y.amount end) < 0 then (sum(case

when y.amount_d_c = 'D' then -y.amount

else y.amount

end)) || ' D'

else (sum (case

when y.amount_d_c = 'D' then -y.amount

else y.amount end)) || ' C'

end as a_amount

from

details y

join value u on y.product_code = u.product_code and
y.symbol = u.symbol
where
y.transaction_date = 1110107

group by
y.num_code,
y.group_code
u.product_code,
y.symbol

The output i get for column amount is like -123456.00 D or 4789546.00 C
I want the D and the C to appear in a different column. Also i want my amount to be an absolute value.

Can the case statement be split into one and add another select statement to my query. Please help!!

tonkuma · #2 (**permalink**) 01-14-11, 08:23

If you put 'D' or 'C' in front of number(i.e. like D 123456.00 or C 4789546.00),
your complex case expressions could be simplified like this:

Code:

     , REPLACE( 'C ' || CHAR(SUM(CASE
                                 WHEN  y.amount_d_c = 'D' THEN
                                      -y.amount
                                 ELSE  y.amount
                                 END ) )
              , 'C -'
              , 'D '
              ) AS a_amount

If you want the result like:

Code:

NUM_CODE ...    ...    D_C A_AMOUNT    
-------- ------ ------ --- ------------
                       D   123456.00
                       C   4789546.00

An answer would be like this:

Code:

SELECT
       ...
     , CASE
       WHEN a_amount < 0 THEN
            'D'
       ELSE 'C'
       END  AS d_c
     , ABS(a_amount) AS a_amount
 FROM  (SELECT
               y.num_code
             , y.group_code
             , u.product_code
             , y.symbol
             , SUM(CASE
                   WHEN  y.amount_d_c = 'D' THEN
                        -y.amount
                   ELSE  y.amount
                   END ) AS a_amount
         FROM  ...
         WHERE ...
         GROUP BY
               ...
       )
;

If you want the result like:

Code:

NUM_CODE ...    ...    D_C C_AMOUNT     D_AMOUNT 
-------- ------ ------ --- ------------ ------------
                       D   -            123456.00
                       C   4789546.00   -

An answer would be like this:

Code:

SELECT
      .....
     , CASE
       WHEN a_amount < 0 THEN
            'D'
       ELSE 'C'
       END  AS d_c
     , CASE WHEN a_amount >= 0 THEN a_amount END AS c_amount
     , NULLIF(ABS(a_amount) , a_amount)          AS d_amount
 FROM ( .....)
;

tonkuma · #3 (**permalink**) 01-14-11, 15:34

Quote:

Code:

     , CASE
       WHEN a_amount < 0 THEN
            'D'
       ELSE 'C'
       END  AS d_c

This will return same result.

Code:

     , SUBSTR('DCC' , SIGN(a_amount) + 2 , 1) AS d_c

deeparavi · #4 (**permalink**) 01-15-11, 17:31

Thanks for getting back.but which query will get the below result.

Let me be clear here. Currently my output is like this:

Numcode Groupcode product_code symbol amount
--- ---- ---- ---- -14523.00 D
--- ---- ---- ---- 25478.00 C

I want the amount column only to appear as an absolute value.(the case should be in place to do the sum)

And i want another column next to amount as amount_a showing D or C

Numcode Groupcode product_code symbol amount amount_a
--- ---- ---- ---- -14523.00 D
--- ---- ---- ---- 25478.00 C

tonkuma · #5 (**permalink**) 01-15-11, 18:29

I already wrote:

Quote:

If you want the result like:

Code:

NUM_CODE ...    ...    D_C A_AMOUNT    
-------- ------ ------ --- ------------
                       D   123456.00
                       C   4789546.00

An answer would be like this:
...

If you want

Quote:

Numcode Groupcode product_code symbol amount amount_a
--- ---- ---- ---- -14523.00 D
--- ---- ---- ---- 25478.00 C

You need only to change the name and the sequence of the result columns.

Another point is...

You wrote "I want the amount column only to appear as an absolute value.".
But, you showed "-14523.00" in your example.

Quote:

I want the amount column only to appear as an absolute value.(the case should be in place to do the sum)

And i want another column next to amount as amount_a showing D or C

Numcode Groupcode product_code symbol amount amount_a
--- ---- ---- ---- -14523.00 D
--- ---- ---- ---- 25478.00 C

I think this is a contradiction.

Anyhow,
if you want to show minus sign(like -14523.00 D),
you only need to change from "ABS(a_amount) AS a_amount" to "a_amount AS amount" in my example.
Or change a name in subquery, like

Code:

SELECT
       ...
     , amount /* ABS(a_amount) AS a_amount */
     , CASE
       WHEN amount /* a_amount */ < 0 THEN
            'D'
       ELSE 'C'
       END  AS amount_a /* d_c */
 FROM  (SELECT
               y.num_code
             , y.group_code
             , u.product_code
             , y.symbol
             , SUM(CASE
                   WHEN  y.amount_d_c = 'D' THEN
                        -y.amount
                   ELSE  y.amount
                   END ) AS amount /* a_amount */
         FROM  ...
         WHERE ...
         GROUP BY
               ...
       )
;

Internet Services

Web Development

Digital Marketing

Consumer Tech