CODEWITHSUNDEEP
sqloracle-databasecasewhere-clause
piyo_lune
piyo_lune

Reputation: 37

How to write a case statement in the Where clause of Oracle SQL?

Please tell me this problem.

TABLE:PPP
+-------+---------+---------+----------+
|No     |  Flag_A | Flag_B  |   Qty    |
+-------+---------+---------+----------+
|100    |    P    |    0    |    10    |
|300    |    B    |    1    |    20    |
|500    |    C    |    0    |    30    |
|100    |    P    |    0    |    40    |
|100    |    P    |    0    |    50    |
|300    |    B    |    1    |    60    |
|500    |    C    |    1    |    70    |
|100    |    P    |    0    |    80    |
|500    |    B    |    2    |    90    |
+-------+---------+---------+----------+

SELECT NO, SUM(QTY) AS QTY 
  FROM PPP
 WHERE 
   CASE FLAG_B IN (
     WHEN FLAG_A = 'P' THEN '0'
     WHEN FLAG_A = 'C' THEN '1'
     WHEN FLAG_A = 'B' THEN '0' , '1' <- how to write? 
    END
   )
GROUP BY NO

I want get this result.

+----+-----+
| No | Qty | 
+----+-----+
| 100|  180|
| 300|   80|
| 500|  100|
+----+-----+

What should i write sql?

Upvotes: 1

Views: 130

Answers (2)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65105

Using DECODE() function containing CASE..WHEN expression in it for the case flag_a='B' might be an alternative way to get the desired results such as

SELECT no, SUM(qty) AS qty
  FROM ppp
 WHERE DECODE(flag_a,'P',0,'C',1,'B', CASE WHEN flag_b IN (0,1) THEN flag_b END)=flag_b
 GROUP BY no

Demo

P.S: I think there's an issue, which needs to fixed, with the data for No=500

Upvotes: 0

GMB
GMB

Reputation: 222382

This would be simpler phrased with boolean logic:

where (flag_a = 'P' and flag_b = 0)
   or (flag_a = 'C' and flag_b = 1)
   or (flag_a = 'B' and flag_b in (0, 1))

We can factorize a little:

where (flag_a in ('B', 'P') and flag_b = 0)
   or (flag_a in ('B', 'C') and flag_b = 1)

Or we can use tuple equality:

where (flag_a, flag_b) in (('P', 0), ('C', 1), ('B', 0), ('B', 1))

flag_b looks like a number, so I compared it against literal numbers; if that's really a string, then you can add back the single quotes around the literals.

Upvotes: 3

Related Questions