I have a SQL case like:
Select Case
when filename like '%bbb%' then 'apple'
when filename like '%aaa%' then 'banan'
When filename like '%ccc%' then 'kiwi'
Else filename
End
I have an output like; apple Banan Kiwi
I should add an SQL code with same filename, but output should have a different name. I need different output from filename ‘bbb’.
This is what I tried:
Select Case
when filename like '%bbb%' then 'apple'
when filename like '%aaa%' then 'banan'
When filename like '%ccc%' then 'kiwi'
When filename like '%bbb%' then 'lemon'
Else filename
End
I need both apple and lemon in the output from filename ‘bbb’.
Expected output:
apple
Banan
Kiwi
Lemon
How can I modify the SQL code?
4
CASE statements work top to bottom, and each row only responds to the first condition that evaluates as true and then it stops processing against the remaining criteria.
For each row it tries the first WHEN. If it is true (‘abc’) then it responds with the THEN (‘ac’), and then moves to the next row in the table. If it is false, then it tries the second WHEN etc.
In your case, the second and third WHEN will not get evaluated, as it already met the first WHEN condition.
In short, if you have multiple of the same condition to evaluate 3x WHEN 'abc', the first one will get used, the others will not get evaluated.
If ‘abc’ is true, the response cannot be ‘ac’, ‘ad’ and ‘ae’. It can only be ‘ac’.
3