Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2004
    Posts
    16

    Unanswered: please suggest modifications to the following a case statement for this

    I have the following case statement in which the last part gives me error ...

    CASE (a.CODE) when 'C6C4D940'
    then sum(f.AMOUNT) else 0 end as Withdrawal_Amt,
    CASE (a.CODE) when 'C6D7C640'
    then sum(f.AMOUNT) else 0 end as Bill_Paymt_Amt,
    CASE (a.CODE) when 'C6E3C640'
    then sum(f.AMOUNT) else 0 end as Transfer_Amt,
    CASE (a.CODE) when 'D8C1C340' or 'D8C9E240' then sum(f.AMOUNT)
    else 0 end as Inquiry,

    When i try to run that it gives me the following error
    SQL0104N An unexpected token "or" was found following "CODE") when 'D8C1C340'".
    Expected tokens may include: "CONCAT". SQLSTATE=42601
    .

    It doesn’t like the ‘or’. I hope somebody can suggest a work-around. Thanks in advance for all your help.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some examples.

    Code:
    CASE a.CODE
    WHEN 'D8C1C340' THEN
         sum(f.AMOUNT)
    WHEN 'D8C9E240' THEN
         sum(f.AMOUNT) 
    ELSE 0
    END  as Inquiry
    or

    Code:
    CASE
    WHEN a.CODE = 'D8C1C340'
     OR  a.CODE = 'D8C9E240' THEN
         sum(f.AMOUNT)
    ELSE 0
    END  as Inquiry
    or

    Code:
    CASE
    WHEN a.CODE
         IN ('D8C1C340' , 'D8C9E240') THEN
         sum(f.AMOUNT)
    ELSE 0
    END  as Inquiry

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I didn't saw whole query, and I don't konw your requirements.
    So, I might make a wrong guess...

    Do you want to do something like this?
    Code:
    SUM(CASE a.CODE
        WHEN 'C6C4D940' THEN
             f.AMOUNT
        ELSE 0
        END) as Withdrawal_Amt,
    ...
    SUM(CASE
        WHEN a.CODE
             IN ('D8C1C340' , 'D8C9E240') THEN
             f.AMOUNT
        ELSE 0
        END) as Inquiry,
    ...
    Last edited by tonkuma; 07-25-10 at 03:13. Reason: Add SUM( ..... ) as Inquiry, ...

  4. #4
    Join Date
    Aug 2004
    Posts
    16

    here is the full query

    here is the full query and part in red does not work

    Select month(a.TXN_TS), year(a.TXN_TS), e.IP_ID, g.ELC_DLVRY_DVC_TP_ID, g.DVC_NBR, b.ABM_ADR_TXT, b.ABM_REFR_NBR, b.ABM_INST_NBR, c.CARD_ACS_NBR,
    a.CODE, f.DDA_CODE,
    CASE (a.CODE) when 'C6C3D940' then sum(f.AMOUNT) else 0 end as Deposit_Amt,
    CASE (a.CODE) when 'C6C4D940'
    then sum(f.AMOUNT) else 0 end as Withdrawal_Amt,
    CASE (a.CODE) when 'C6D7C640' then sum(f.AMOUNT) else 0 end as Bill_Paymt_Amt,
    CASE (a.CODE) when 'C6E3C640' then sum(f.AMOUNT) else 0 end as Transfer_Amt,
    CASE (a.CODE) when 'D8C1C340' or 'D8C9E240' then sum(f.AMOUNT)
    else 0 end as Inquiry,
    CASE (f.DDA_CODE) when 'OM' then sum(f.AMOUNT) else 0 end as Other_ABM_Amt,
    CASE (f.DDA_CODE) when 'SC' then sum(f.AMOUNT) else 0 end as Servc_Charge_Amt, count(distinct a.TXN_ID) as Nbr_Txns
    from PRDINS.TXN a INNER JOIN PRDINS.ABM_TXN_CNL b
    ON a.TXN_ID = b.TXN_ID
    INNER JOIN PRDINS.TXN_ACS_FCY c
    ON a.TXN_ID = c.TXN_ID
    INNER JOIN PRDINS.AR_X_IP_RLTNP d
    ON d.LOB_AC_ID = c.CARD_ACS_NBR
    INNER JOIN PRDINS.IP e
    ON d.IP_ID = e.IP_ID
    INNER JOIN PRDINS.ELC_TXN_DLVRY_CNL g
    ON b.TXN_ID = g.TXN_ID
    LEFT OUTER JOIN PRDINS.PST_ENTR f
    ON b.TXN_ID = f.TXN_ID
    where month(a.TXN_TS) = 6 and year(a.TXN_TS) = 2010
    and a.TXN_COMPL_IND = 'Y'
    and e.SRC_STM_ID = 2
    GROUP BY month(a.TXN_TS), year(a.TXN_TS), e.IP_ID, g.ELC_DLVRY_DVC_TP_ID, g.DVC_NBR, b.ABM_ADR_TXT, b.ABM_REFR_NBR, b.ABM_INST_NBR, c.CARD_ACS_NBR,
    a.CODE, f.DDA_CODE
    ORDER BY count(distinct a.TXN_ID) desc

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    mspahwa, what tonkuma gave you fixes your problem.

    The situation is the CASE Expression has two formats. They are Simple When and Searched When.

    The Simple When is what you are using. The syntax is:

    CASE expression WHEN expression THEN result-expression ELSE result-expression END

    With this format the CASE expression is compared to the WHEN expression. If it is Equal, then it is True. If it is Not Equal it is False and the When is skipped and either the next When (if any) is evaluated

    The Searched When is what Tonkuma showed you. The syntax is:

    CASE WHEN search-condition THEN result-expression ELSE result-expression END

    With this format there is no Case expression that is used by the WHEN clauses. Every WHEN search-condition is evaluated only on the items associated with it. If all conditions evaluate out to True, the THEN result-expression is used. If they evaluate out to False, the When is skipped and either the next When (if any) is evaluated.

    As an example, a Simple When has one 'value' and each WHEN is checked against that value and it can only be an equality check (equal-True, not equal-False). It is limited in what you can do.
    Code:
    CASE MONTH(CURRENT DATE)
      WHEN  1 THEN ...
      WHEN  2 THEN ...
      WHEN  3 THEN ...
      (etc.)
    END
    A Searched When can have a lot of flexibility in what you can do as you can use Boolean login (AND, OR, etc.).

    Code:
    CASE
      WHEN (col1 = 'AA' AND col2 < 5) or (col3 between 1 and 10 and col4 like 'XYZ%' THEN ...
      WHEN col5 >= 8 THEN ...
      WHEN col6 < current data - 6 months THEN ...
      (etc.)
    END
    As you can see, you are not limited to only Equal / Not equal comparison. And you don't even need to make the same type of comparisons in every WHEN (in my example, the first When checks col1-col4, the second When check col5 and the third When checks col6.

    Getting back to your problem, this line:
    Code:
    CASE (a.CODE) when 'D8C1C340' or 'D8C9E240' then sum(f.AMOUNT)
    has a Simple When format
    CASE A.CODE
    but the WHEN has multiple values it is checking (You can only compare one item)
    WHEN 'D8C1C340' or 'D8C9E240'

    Tonkuma gave you 3 ways to fix it.

    A) keep your Simple When by moving the multiple items to separate WHEN clause.

    B & C) change the Simple When to a Searched When and use either an Or or In to compare for the multiple items.

  6. #6
    Join Date
    Aug 2004
    Posts
    16
    thank you all

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    mspahwa,

    Looking into your query, you will get sum(f.AMOUNT) for each combination of a.CODE and f.DDA_CODE,
    like the following example:
    (Although it may be your way, I felt that the format of the result was a strange.)

    (Order of the result may not be same as the following example.
    Because count(distinct a.TXN_ID) will be different for each combination of a.CODE and f.DDA_CODE.)
    Code:
    1     2     IP_ID     ... CODE     DDA_CODE DEPOSIT_AMT WITHDRAWAL_AMT BILL_PAYMT_AMT TRANSFER_AMT INQUIRY OTHER_ABM_AMT SERVC_CHARGE_AMT NBR_TXNS
    ----- ----- --------- ... -------- -------- ----------- -------------- -------------- ------------ ------- ------------- ---------------- --------
    6     2010  aaa...zzz     C6C3D940 OM       xxxxx       0              0              0            0       xxxxx         0                ...
    6     2010  aaa...zzz     C6C3D940 SC       yyyyy       0              0              0            0       0             yyyyy            ...
    6     2010  aaa...zzz     C6C3D940 jj       zzzzz       0              0              0            0       0             0                ...
    6     2010  aaa...zzz     C6C3D940 kk       .....       0              0              0            0       0             0                ...
    6     2010  aaa...zzz     C6C3D940 -        .....       0              0              0            0       0             0                ...
    
    6     2010  aaa...zzz     C6C4D940 OM       0           uuuuu          0              0            0       uuuuu         0                ...
    6     2010  aaa...zzz     C6C4D940 SC       0           vvvvv          0              0            0       0             vvvvv            ...
    6     2010  aaa...zzz     C6C4D940 jj       0           wwwww          0              0            0       0             0                ...
    6     2010  aaa...zzz     C6C4D940 kk       0           .....          0              0            0       0             0                ...
    6     2010  aaa...zzz     C6C4D940 -        0           .....          0              0            0       0             0                ...
    
    .....
    .....

  8. #8
    Join Date
    Aug 2004
    Posts
    16
    you are right , i do not want it this way i want to exclude code from the group by statement and just get the sum(f.AMOUNT) just for f.DDA_CODE others how can i do that...thanks for your help

    Quote Originally Posted by tonkuma View Post
    mspahwa,

    Looking into your query, you will get sum(f.AMOUNT) for each combination of a.CODE and f.DDA_CODE,
    like the following example:
    (Although it may be your way, I felt that the format of the result was a strange.)

    (Order of the result may not be same as the following example.
    Because count(distinct a.TXN_ID) will be different for each combination of a.CODE and f.DDA_CODE.)
    Code:
    1     2     IP_ID     ... CODE     DDA_CODE DEPOSIT_AMT WITHDRAWAL_AMT BILL_PAYMT_AMT TRANSFER_AMT INQUIRY OTHER_ABM_AMT SERVC_CHARGE_AMT NBR_TXNS
    ----- ----- --------- ... -------- -------- ----------- -------------- -------------- ------------ ------- ------------- ---------------- --------
    6     2010  aaa...zzz     C6C3D940 OM       xxxxx       0              0              0            0       xxxxx         0                ...
    6     2010  aaa...zzz     C6C3D940 SC       yyyyy       0              0              0            0       0             yyyyy            ...
    6     2010  aaa...zzz     C6C3D940 jj       zzzzz       0              0              0            0       0             0                ...
    6     2010  aaa...zzz     C6C3D940 kk       .....       0              0              0            0       0             0                ...
    6     2010  aaa...zzz     C6C3D940 -        .....       0              0              0            0       0             0                ...
    
    6     2010  aaa...zzz     C6C4D940 OM       0           uuuuu          0              0            0       uuuuu         0                ...
    6     2010  aaa...zzz     C6C4D940 SC       0           vvvvv          0              0            0       0             vvvvv            ...
    6     2010  aaa...zzz     C6C4D940 jj       0           wwwww          0              0            0       0             0                ...
    6     2010  aaa...zzz     C6C4D940 kk       0           .....          0              0            0       0             0                ...
    6     2010  aaa...zzz     C6C4D940 -        0           .....          0              0            0       0             0                ...
    
    .....
    .....
    Last edited by mspahwa; 08-03-10 at 14:51.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Remove a.CODE and f.DDA_CODE from GROUP BY clause and SELECT list,
    then use case expressions in SUM functions like I suggested earlier.

    I didn't saw whole query, and I don't konw your requirements.
    So, I might make a wrong guess...

    Do you want to do something like this?

    Code:
    SUM(CASE a.CODE
        WHEN 'C6C4D940' THEN
             f.AMOUNT
        ELSE 0
        END) as Withdrawal_Amt,
    ...
    SUM(CASE
        WHEN a.CODE
             IN ('D8C1C340' , 'D8C9E240') THEN
             f.AMOUNT
        ELSE 0
        END) as Inquiry,
    ...
    and

    SUM(CASE f.DDA_CODE WHEN 'OM' THEN f.AMOUNT ELSE 0 END) as Other_ABM_Amt,
    SUM(CASE f.DDA_CODE WHEN 'SC' THEN f.AMOUNT ELSE 0 END) as Servc_Charge_Amt,
    Last edited by tonkuma; 08-03-10 at 19:16. Reason: Add "in SUM functions"

Posting Permissions

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