where to use SQL Case statement ? and when ?and give example?

[gosai jahnvi]

where to use SQL Case statement ? and when ?and give example?

[Luuk]

You can use it to select even or odd numbers:

SELECT 
	i,
	CASE WHEN i%2=1 THEN i ELSE 0 END  AS odd,
	CASE WHEN i%2=0 THEN i ELSE 0 END  AS even
FROM TEST
WHERE i<1000

output:

i	odd	even
1	1	0
2	0	2
3	3	0
4	0	4
5	5	0
6	0	6
7	7	0
8	0	8
9	9	0
10	0	10
11	11	0

In simple cases (like above) there are some alternatives like IIF:

SELECT 
	i,
	IIF (i%2=1, i, 0)  AS odd,
	IIF (i%2=0, i, 0)  AS even
FROM TEST
WHERE i<12

but when expanding this example, you will no longer want to write this using IIF:

SELECT 
	i,
	CASE i%4 
           WHEN 3 THEN 3 
           WHEN 2 THEN 2 
           WHEN 1 THEN 1 
           ELSE 0 END AS remainderAfterDivBy4
FROM TEST
WHERE i<12

output:

i	remainderAfterDivBy4
1	1
2	2
3	3
4	0
5	1
6	2
7	3
8	0
9	1
10	2
11	3

[Rabbit]

Where?
Pretty much anywhere, in the select clause, or a join clause, or a where clause, or an order by clause, etc.

When?
Whenever you need to output different values based on a condition.

Examples?
See Luuk's response above.