Results 1 to 5 of 5

Thread: select issue

  1. #1
    Join Date
    Jul 2010
    Posts
    6

    Unanswered: select issue

    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!!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 ( .....)
    ;

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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

  4. #4
    Join Date
    Jul 2010
    Posts
    6

    whitney. corecoar

    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

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I already wrote:

    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
    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.
    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
                   ...
           )
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •