case 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;

1
2  SELECT OrderID, Quantity,
3CASE
4    WHEN Quantity > 30 
5  THEN 'The quantity is greater than 30'
6    WHEN Quantity = 30 THEN 'The 
7  quantity is 30'
8    ELSE 'The quantity is under 30'
9END AS QuantityText
10FROM OrderDetails;