please suggest modifications to the following a case statement for this

mspahwa · #1 (**permalink**) 07-24-10, 23:34

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.

tonkuma · #2 (**permalink**) 07-25-10, 01:12

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

tonkuma · #3 (**permalink**) 07-25-10, 01:27

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,
...

mspahwa · #4 (**permalink**) 07-25-10, 15:32

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

Stealth_DBA · #5 (**permalink**) 07-25-10, 18:30

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.

mspahwa · #6 (**permalink**) 07-25-10, 18:57

thank you all

tonkuma · #7 (**permalink**) 07-26-10, 04:13

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

.....
.....

mspahwa · #8 (**permalink**) 08-03-10, 11:52

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

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

.....
.....

tonkuma · #9 (**permalink**) 08-03-10, 18:08

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.

Quote:

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,

Internet Services

Web Development

Digital Marketing

Consumer Tech