In Case statement order of when condition matters?

[nsaxena]

Hi All ,
I have one table which i am using in report. From that report i need to add one additional query item based on existing column .
Suppose we have columns name  like A,B,C,D,E,F and then we need to derive new column with case statement like
case 1
when A='p' then '1'
when  B='z' then '2'
end

my requirement : if for a row value of column A is p then it should set new item value as 1 and should ignore other when condition .
now if column value for a is not p then it should come to second case when condition when B='q' then '3' and read it was for all rows whose A<>'p' and B='q'  set value as 3 .
so basically i want to know does order of when condition matters or i have to re write above case as to get my required logic

case 1
when A='p' then '1'
when  A<>'P and 'B='z' then '2'
end
Similarly many combinations of other columns are used in implementing the logic of new query item.

Kindly share opinion or suggests any other way to get this.

Thanks in advance!!

[cognos810]

Hello nsaxena,
Yes, the order of when clause matters in this case. For every record, the case will be executed only till it finds a match that resolves to a condition. If the value of A is 'p' the respective THEN clause will be applied and the CASE will break and jump out. If it is not found, it will go to B, then C, so on and so forth. If nothing matches, then it goes to ELSE(if you have provided one). If there is no ELSE, a NULL value is applied.
In short, for your requirement it will work perfectly fine if you keep the WHEN clause checks for A,B,C,D, E and F in that order.

The order does NOT matter, in cases like...
CASE
WHEN 'A' = 'p' THEN 1
WHEN 'A' = 'q' THEN 2
WHEN 'A' ='r' THEN 3
.
.
END
as there can be only one possible value for A and only one condition can be true at a time.

Hope it helps,
Cognos810

[nsaxena]

Hi Cognos810 ,

Many thanks for your detail reply...it explains well!! :)