case when or sql code example

Example 1: sql case

-- NOTE: this is for SQL-Oracle specifically

/*
NB: Please like Mingles444 post, I derived this from him/her
*/

-- syntax: (Retrieved from grepper:Mingles444)
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END 

-- example:
SELECT 
	CASE
      WHEN (1+6 = 6) THEN 'A'
      WHEN (1+6 = 7) THEN 'B'
      WHEN (1+6 = 8) THEN 'C'
      ELSE 'D'
	END 
FROM DUAL;

-- OUTPUT: B

Example 2: sql case

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

Example 3: case when switch in SQL

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