sql case statement in select 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: end as sql
select
case when ID in ('1', '2', '3')
then 'Jack'
else 'Jim'
end as Person
from Table.Names
select
case when ID in ('1', '2', '3')
then 'Jack'
else 'Jim'
end Person
from Table.Names
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