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