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!!
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
Hi Cognos810 ,
Many thanks for your detail reply...it explains well!! :)