case statement in sql

1-- NOTE: this is for SQL-Oracle specifically
2
3/*
4NB: Please like Mingles444 post, I derived this from him/her
5*/
6
7-- syntax: (Retrieved from grepper:Mingles444)
8CASE
9    WHEN condition1 THEN result1
10    WHEN condition2 THEN result2
11    WHEN conditionN THEN resultN
12    ELSE result
13END 
14
15-- example:
16SELECT 
17	CASE
18      WHEN (1+6 = 6) THEN 'A'
19      WHEN (1+6 = 7) THEN 'B'
20      WHEN (1+6 = 8) THEN 'C'
21      ELSE 'D'
22	END 
23FROM DUAL;
24
25-- OUTPUT: B
26

1-- Case Eg.) to retrive the MAX value of a Field 
2-- if there are entries for the Field in table MAX value will be returned 
3-- But if there is no entries at all for the Field in tabel MAX will return
4-- Null as the output. But Using Case When we can check it out return zero 
5-- or any other value if there is no enties for the Field in table..
6SELECT 
7CASE   -- Like Switch Case
8	WHEN -- First When condition 
9		(MAX(BILLID) IS NULL) -- Condition 
10	THEN 1   -- output   (We can also add more When conditions like Above)
11ELSE -- When WHEN Condition not Satisfied Below will be Executed. 
12		(MAX(BILLID)) -- output
13END 
14as MAXBILLID   from  DUAL;
15-- Final Output
16-- If there is no entry in the Field for the table
17-- BILLID
18--  1
19-- If there are entries MAX of that Field value from the table
20-- BILLID
21-- 10

1/*CASE statements are used to create different outputs and is 
2  used by SQL as a way to handle if-then logic.*/
3  
4  SELECT column_name,
5    CASE 
6      WHEN condition THEN 'Result_1'
7      WHEN condition THEN 'Result_2'
8      ELSE 'Result_3'
9    END
10  FROM table_name;

1select 
2case when ID in ('1', '2', '3')
3then 'Jack'
4else 'Jim'
5end as Person
6from Table.Names
7
8select
9case when ID in ('1', '2', '3')
10then 'Jack'
11else 'Jim'
12end Person
13from Table.Names

1Change query output depending on conditions.
2Example: Returns users and their subscriptions, along with a new column
3called activity_levels that makes a judgement based on the number of
4subscriptions.
5SELECT first_name, surname, subscriptions
6CASE WHEN subscriptions > 10 THEN 'Very active'
7WHEN Quantity BETWEEN 3 AND 10 THEN 'Active'
8ELSE 'Inactive'
9END AS activity_levels
10FROM users;

1Case Statement basically
2Like IF - THEN - ELSE statement.
3
4The CASE statement goes through conditions
5and returns a value when the
6first condition is met and
7once a condition is true,
8it will stop reading and return the result.
9If no conditions are true,
10it returns the value in the ELSE clause.
11
12If there is no ELSE part and
13no conditions are true, it returns NULL.
14
15FOR EXAMPLE =
16
17CASE
18    WHEN condition1 THEN result1
19    WHEN condition2 THEN result2
20    WHEN conditionN THEN resultN
21    ELSE result
22END 
23
24-- example:
25SELECT 
26	CASE
27      WHEN (1+6 = 6) THEN 'A'
28      WHEN (1+6 = 7) THEN 'B'
29      WHEN (1+6 = 8) THEN 'C'
30      ELSE 'D'
31	END 
32FROM DUAL;
33
34Result would be 'B' since it is the first
35correct answer