The SQL CASE statement allows you to perform IF-THEN-ELSE functionality within an SQL statement. Learn more about this powerful statement in this article.
This article applies to Oracle, SQL Server, MySQL, and PostgreSQL.
What Does the SQL CASE Statement Do?
The CASE statement allows you to perform an IF-THEN-ELSE check within an SQL statement.
It’s good for displaying a value in the SELECT query based on logic that you have defined. As the data for columns can vary from row to row, using a CASE SQL expression can help make your data more readable and useful to the user or to the application.
It’s quite common if you’re writing complicated queries or doing any kind of ETL work.
SQL CASE Statement Syntax
The syntax of the SQL CASE expression is:
CASE [expression]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2 ...
WHEN condition_n THEN result_n
ELSE result
END case_nameThe CASE statement can be written in a few ways, so let’s take a look at these parameters.
Parameters of the CASE Statement
The parameters or components of the CASE SQL statement are:
- expression (optional): This is the expression that the CASE statement looks for. If we’re comparing this to an IF statement, this is the check done inside the IF statement (e.g. for IF x > 10, the expression would be “x > 10”
- condtion_1/condition_n (mandatory): These values are a result of the expression parameter mentioned. They are the possible values that expression can evaluate to. Alternatively, they can be an expression on their own, as there are two ways to write an SQL CASE statement (as explained below). They also relate to the IF statement in the IF-THEN-ELSE structure.
- result_1/result_n (mandatory): These values are the value to display if the related condition is matched. They come after the THEN keyword and relate to the THEN part of the IF-THEN-ELSE structure.
- result (optional): This is the value to display if none of the conditions in the CASE statement are true. It is the ELSE part of the IF-THEN-ELSE structure and is not required for the CASE SQL statement to work.
- case_name (optional): This value indicates what the column should be referred to as when displayed on the screen or from within a subquery. It’s also called the column alias.
While we’re here, if you want an easy-to-use PDF guide for the main features in different database vendors, get my SQL Cheat Sheets here:
Simple and Searched CASE Expressions
There are actually two ways to use an SQL CASE statement, which are referred to as a “simple case expression” or a “searched case expression”.
Simple CASE expression
The expression is stated at the beginning, and the possible results are checked in the condition parameters.
For example:
CASE name
WHEN 'John' THEN 'Name is John'
WHEN 'Steve' THEN 'Name is Steve'
END
Searched CASE expression
The expressions are used within each condition without mentioning it at the start of the CASE statement.
For example:
CASE
WHEN name = 'John' THEN 'Name is John'
WHEN name = 'Steve' THEN 'Name is Steve'
ENDAll data types for the expression and conditions for the Simple expressions, and all of the results for both expression types must be the same or have a numeric data type. If they all are numeric, then the database will determine which argument has the highest numeric precedence, implicitly convert the remaining argument to that data type, and return that datatype. Basically, it means the database will work out which data type to return for this statement if there is a variety of numeric data types (NUMBER, BINARY_FLOAT or BINARY_DOUBLE for example).
In SQL, IF statements in SELECT statements can be done with either of these methods. I’ll demonstrate this using more examples later in this article.
Want to see guides like this for all other Oracle functions? Check out this page here that lists all SQL functions along with links to their guides.
Examples of the CASE Statement
Here are some examples of the SQL CASE statement in SELECT queries. I find that examples are the best way for me to learn about code, even with the explanation above.
Simple CASE Statement
SELECT
first_name, last_name, country,
CASE country
WHEN 'USA' THEN 'North America'
WHEN 'Canada' THEN 'North America'
WHEN 'UK' THEN 'Europe'
WHEN 'France' THEN 'Europe'
ELSE 'Unknown'
END Continent
FROM customers
ORDER BY first_name, last_name;The results are:
| FIRST_NAME | LAST_NAME | COUNTRY | CONTINENT |
| Adam | Cooper | USA | North America |
| John | Smith | USA | North America |
| Mark | Allan | UK | Europe |
| Sally | Jones | USA | North America |
| Steve | Brown | Canada | North America |
This example is using the simple case statement structure. Notice how the expression (in this case the “country” field) comes right after the CASE keyword. This means the WHEN expressions are all compared to that field.
This example, like most of the examples here, shows the continent of each customer, based on their country.
Searched Case
SELECT first_name, last_name, country,
CASE
WHEN country = 'USA' THEN 'North America'
WHEN country = 'Canada' THEN 'North America'
WHEN country = 'UK' THEN 'Europe'
WHEN country = 'France' THEN 'Europe'
ELSE 'Unknown'
END Continent
FROM customers
ORDER BY first_name, last_name;| FIRST_NAME | LAST_NAME | COUNTRY | CONTINENT |
| Adam | Cooper | USA | North America |
| John | Smith | USA | North America |
| Mark | Allan | UK | Europe |
| Sally | Jones | USA | North America |
| Steve | Brown | Canada | North America |
This example performs the same check as the other examples but uses the searched case method. This means that each expression in the WHEN section is evaluated individually. It gives the same result as the previous example though.
Searched Case with Numbers
SELECT first_name, last_name, employees,
CASE
WHEN employees < 10 THEN 'Small'
WHEN employees >= 10 AND employees <= 50 THEN 'Medium'
WHEN employees >= 50 THEN 'Large'
END SizeOfCompany
FROM customers
ORDER BY first_name, last_name;| FIRST_NAME | LAST_NAME | EMPLOYEES | SIZEOFCOMPANY |
| Adam | Cooper | 55 | Large |
| John | Smith | 4 | Small |
| Mark | Allan | 23 | Medium |
| Sally | Jones | 10 | Medium |
| Steve | Brown | 15 | Medium |
This example performs a searched CASE using a number field, which is the number of employees. Notice how the second WHEN expression has two checks – to see if the number is between 10 and 50.
CASE Statement With IN Clause
SELECT first_name, last_name, country,
CASE
WHEN country IN ('USA', 'Canada') THEN 'North America'
WHEN country IN ('UK', 'France') THEN 'Europe'
ELSE 'Unknown'
END Continent
FROM customers
ORDER BY first_name, last_name;| FIRST_NAME | LAST_NAME | COUNTRY | CONTINENT |
| Adam | Cooper | USA | North America |
| John | Smith | USA | North America |
| Mark | Allan | UK | Europe |
| Sally | Jones | USA | North America |
| Steve | Brown | Canada | North America |
This example looks up the continent of the customer again. However, it uses an IN clause, which means the value is checked to see if it is in the IN parameter. It should have the same result, but it’s a bit cleaner and has less code.
Nested CASE Statement in SQL
This example shows a CASE statement within another CASE statement, also known as a “nested case statement” in SQL.
It first checks the country and then checks for a particular customer name to see if it is male or female (given that Sally is the only female here).
Notice how I didn’t give a name to the inner case statement. I didn’t need to – this is not displayed and the name is already specified for the Continent column.
SELECT first_name, last_name, country,
CASE
WHEN country IN ('USA', 'Canada') THEN
(CASE WHEN first_name = 'Sally' THEN 'North America F' ELSE 'North America M' END)
WHEN country IN ('UK', 'France') THEN
(CASE WHEN first_name = 'Sally' THEN 'Europe F' ELSE 'Europe M' END)
ELSE 'Unknown'
END Continent
FROM customers
ORDER BY first_name, last_name;| FIRST_NAME | LAST_NAME | COUNTRY | CONTINENT |
| Adam | Cooper | USA | North America M |
| John | Smith | USA | North America M |
| Mark | Allan | UK | Europe M |
| Sally | Jones | USA | North America F |
| Steve | Brown | Canada | North America M |
We can see that the results show different values based on the nested CASE statement.
CASE Statement with Functions
SELECT first_name, last_name, employees,
CASE
WHEN MOD(employees, 2) = 0 THEN 'Even Number of Employees'
WHEN MOD(employees, 2) = 1 THEN 'Odd Number of Employees'
ELSE 'Unknown'
END OddOrEven
FROM customers
ORDER BY first_name, last_name;| FIRST_NAME | LAST_NAME | EMPLOYEES | ODDOREVEN |
| Adam | Cooper | 55 | Odd Number of Employees |
| John | Smith | 4 | Even Number of Employees |
| Mark | Allan | 23 | Odd Number of Employees |
| Sally | Jones | 10 | Even Number of Employees |
| Steve | Brown | 15 | Odd Number of Employees |
This example uses the MOD function to demonstrate how you can use CASE statements with functions. It checks the number of employees and determines if they have an odd or even number of employees.
Multiple Matches
SELECT first_name, last_name, employees,
CASE
WHEN employees < 1 THEN 'No Employees'
WHEN employees < 10 THEN 'Small'
WHEN employees <= 50 THEN 'Medium'
WHEN employees >= 50 THEN 'Large'
END SizeOfCompany
FROM customers
ORDER BY first_name, last_name;| FIRST_NAME | LAST_NAME | EMPLOYEES | SIZEOFCOMPANY |
| Adam | Cooper | 55 | Large |
| John | Smith | 4 | Small |
| Mark | Allan | 23 | Medium |
| Sally | Jones | 10 | Medium |
| Steve | Brown | 15 | Medium |
This example shows what happens if there are records that match with multiple WHEN expressions. For example, some customers may have both <1 employees and <10 employees. What happens here? The CASE statement finds the first matching expression and uses that.
CASE Statement in WHERE Clause
SELECT first_name, last_name, country
FROM customers
WHERE
(CASE
WHEN country IN ('USA', 'Canada') THEN 'North America'
WHEN country IN ('UK', 'France') THEN 'Europe'
ELSE 'Unknown'
END) = 'North America'
ORDER BY first_name, last_name;| FIRST_NAME | LAST_NAME | COUNTRY |
| Adam | Cooper | USA |
| John | Smith | USA |
| Sally | Jones | USA |
| Steve | Brown | Canada |
This example shows how the CASE statement is used in a WHERE clause.
This example shows all customers who live in North America, using the CASE statement to restrict the records.
CASE Statement Frequently Asked Questions
What’s an IF Statement?
In case you’re not sure, an IF statement allows you to do something if a condition is true, and something else if the condition is false.
It’s a common feature of many programming languages.
However, SQL isn’t like other programming languages.
It’s not procedural. It doesn’t make several steps on different variables to get the result you want.
It’s set based. You tell the database everything you want, and it returns a set of results.
So, how can you have an SQL IF statement?
I’ll be writing about how to write the IF statement in SQL.
What If No Match Is Found In A CASE Statement?
If there is no match found in any of the conditions, that’s where the ELSE statement comes in. The value used in the ELSE statement is what is returned if no match is found.
However, this is an optional part of the SQL CASE statement. If there is no result, and there is no ELSE statement, then the value of NULL is returned.
Does The CASE Statement Search All Conditions Or Just Finds The First Match?
A common question on SQL CASE statements is if the database evaluates all of the conditions in the CASE statement, or does it stop after finding the first match?
The answer is that it stops after the first match. It finds the first match, or the first expression that is evaluated to be a match, and does not continue with the rest.
It also performs something called “short-circuit evaluation” for Simple CASE expressions. This might not be a concern to you, but it’s good to know for performance reasons. The database will evaluate the first condition, then compare it to the expression, then evaluate the second condition, then evaluate that to the expression, and so on. It doesn’t evaluate all conditions before comparing the first one to the expression.
How Many Conditions or Arguments Can a CASE Statement Use?
The maximum number of conditions in a CASE statement is 255. This includes:
- The initial expression in a simple CASE statement
- The optional ELSE expression
- The expression for the WHEN condition
- The expression for the THEN result
You can use nested CASE statements so that the return value is a CASE expression. However, if you’re reaching the limit of 255 expressions, I would be looking at the efficiency of the query itself, as most queries should not need 255 expressions.
Can You Use An SQL CASE Statement In WHERE Clause?
Yes, you can use an SQL CASE in a WHERE clause. The examples below will show how this is done.
Can You Use An SQL CASE within CASE?
Yes, you can use a CASE within CASE in SQL. The examples below will show how this is done.
Can I Use DECODE Instead Of CASE?
Oracle has a function called DECODE, which lets you check an expression and return different values.
It looks like this:
DECODE (expression, condition1, result1, condition_n, result_n)However, CASE is recommended for several reasons:
- DECODE is older, and CASE was made as a replacement for DECODE.
- CASE offers more flexibility.
- CASE is easier to read.
In terms of performance, they are both very similar. Experiments have shown that unless you’re using millions of records, you won’t get much of a difference, and any difference will be small.
MySQL has a DECODE function but it’s used for something completely different. SQL Server and PostgreSQL don’t have a DECODE function.
There Is No IIF or IF in Oracle
SQL Server 2012 introduced a statement called IIF, which allows for an IF statement to be written.
However, Oracle does not have this functionality. It’s SQL Server only.
The CASE statement should let you do whatever you need with your conditions.
In Oracle, there is no “IF” statement or keyword specifically in Oracle. If you want to use IF logic, then use the CASE statement.
Procedural Languages Have an IF Statement
The procedural languages for each database do have an IF statement:
- Oracle PL/SQL
- SQL Server T-SQL
- MySQL
- PostgreSQL PGSQL
This statement works just like other languages. You have IF, ELSE, ELSIF and END.
However, that’s when you’re working with PL/SQL.
If you’re writing functions or stored procedures, you could use this IF statement.
If you’re just using standard SQL in your application or database, then you can use the CASE statement.
Conclusion
Hopefully, that explains how the SQL CASE statement is used and answers any questions you had. If you want to know more, just leave a comment below.
If you want an easy-to-use PDF guide for the main features in different database vendors, get my SQL Cheat Sheets here:
Is it possible to use the same CASE statement for both the SELECT clause and the WHERE clause? For example, using the “continent” example above, could you add something along the lines of WHERE CONTINENT = “Europe”?
Hi Gregg, yes you can use a CASE statement in both the SELECT and WHERE clauses if you wanted to.
I know you can use the CASE statement in either. My question is if you can use the SAME CASE statement in both places in the SAME query, with one referencing the other.
This is a nonsensical example, but could you do something like this:?
SELECT first_name, last_name, country,
CASE country
WHEN ‘USA’ THEN ‘North America’
WHEN ‘Canada’ THEN ‘North America’
WHEN ‘UK’ THEN ‘Europe’
WHEN ‘France’ THEN ‘Europe’
ELSE ‘Unknown’
END Continent
FROM customers
>>>> WHERE Continent like ‘%America’ <<<<
ORDER BY first_name, last_name;
Again, I recognize you wouldn't write this exact query. I'm just looking conceptually at referencing the CASE statement in the SELECT clause somewhere in the WHERE clause, or vice versa.
Ah, I see what you mean.
You can’t reference the CASE statement like the example you gave, because it’s referring to a column alias, which can’t be done inside a WHERE clause. If you want to use the CASE statement in the WHERE clause, you’ll need to copy and paste the same CASE statement, instead of use the “continent” name.
Hi sir i am Bujjibabu from india If don’t mind I want Oracle projects sir please provide me for my practical sir
Hi Ben,
Thank you so much for this post. It is great because It is what I am looking for.
Hi Ben,
Your article is the most complete I have found on the internet discussing CASE. I have some difficult code that I have inherited and has terrible performance time. I want to redo the following using CASE. However, as I said, it is difficult. I think the AVG function and the COUNT might make it impossible. I might need to use nested CASEs.(?) Could you please tell me how to do this or where to start. The code is very similar on both sides of the UNION ALL. Thank you very much,
Margaret
select d.seq, ‘Topo’ “Layer Type”, “Avg” from
(select 1 seq,trunc(avg(count)) “Avg” from (select to_char(dldate,’YYYY-MM’), count(*) count from GRAPHICS_DOWNLOAD g where itcl_id
in
(select ic.id from item_class_data ic
where ic.product_type in (‘Graphics’) and ic.product_theme=’US Topo’)
and exists (select ‘x’ from CELL_STATES cs where cs.cell_id=g.cell_id
and cs.name like ‘%’||:P835_STATE||’%’)
group by to_char(dldate,’YYYY-MM’))) d
union all
select d.seq, ‘Scan Map’ “Layer Type”, “Avg” from
(select 2 seq,trunc(avg(count)) “Avg” from (select to_char(dldate,’YYYY-MM’), count(*) count from GRAPHICS_DOWNLOAD g where itcl_id
=
163
and exists (select ‘x’ from CELL_STATES cs where cs.cell_id=g.cell_id
and cs.name like ‘%’||:P835_STATE||’%’)
group by to_char(dldate,’YYYY-MM’))) d
union all
select d.seq, ‘Historical’ “Layer Type”, “Avg” from
(select 4 seq,trunc(avg(count)) “Avg” from (select to_char(dldate,’YYYY-MM’), count(*) count
from GRAPHICS_DOWNLOAD g where itcl_id
in
(select ic.id from item_class_data ic
where ic.product_type in (‘Graphics’) and ic.product_theme=’Hist’)
and (exists (select ‘x’ from CELL_STATES cs where cs.cell_id=g.cell_id
and cs.name like ‘%’||:P835_STATE||’%’)
or :P835_STATE=’%’
or (g.cell_id is null and :P835_STATE in (‘%’,’MP’)))
group by to_char(dldate,’YYYY-MM’))) d
order by 1
Hi Margaret,
Thanks for the comment. I’ve had a look at your query and yes I think it can be improved with CASE. I’ve updated it here.
I created some test data and it seemed to work for me with a performance improvement. Does it work for you?
SELECT *
FROM (
SELECT
(AVG(NULLIF(count_topo, 0))) AS avg_topo,
(AVG(NULLIF(count_scan_map, 0))) AS avg_scanmap,
(AVG(NULLIF(count_hist, 0))) AS avg_hist
FROM (
SELECT
dl_month,
SUM(count_topo) AS count_topo,
SUM(count_scan_map) AS count_scan_map,
SUM(count_hist) AS count_hist
FROM (
SELECT
dl_month,
CASE WHEN sub.product_theme = ‘US Topo’ AND sub.itcl_id != 163 THEN 1 ELSE 0 END count_topo,
CASE WHEN sub.itcl_id = 163 THEN 1 ELSE 0 END count_scan_map,
CASE WHEN sub.product_theme = ‘Hist’ AND sub.itcl_id != 163 THEN 1 ELSE 0 END count_hist
FROM (
SELECT TO_CHAR(g.dldate,’YYYY-MM’) AS dl_month,
g.itcl_id,
g.cell_id,
ic.product_theme
FROM graphics_download g
INNER JOIN item_class_data ic ON g.itcl_id = ic.id
WHERE (
(
ic.product_type = ‘Graphics’
AND ic.product_theme IN (‘US Topo’, ‘Hist’)
AND g.itcl_id != 163
)
OR (
g.itcl_id = 163
)
)
AND (
EXISTS (
SELECT ‘x’
FROM cell_states cs
WHERE cs.cell_id = g.cell_id
AND cs.name LIKE ‘%’||:P835_STATE||’%’
)
OR :P835_STATE=’%’
OR (g.cell_id IS NULL AND :P835_STATE IN (‘%’,’MP’))
)
) sub
) sub2
GROUP BY dl_month
) sub3
)
UNPIVOT (avg_val FOR seq IN (avg_topo AS 1, avg_scanmap AS 2, avg_hist AS 4))
;
Thanks,
Ben
Ben, That is exactly what I needed to know! You made it make sense. You know how sometimes when you think about something your brain starts to go in circles? Well, you opened a way out.
You did it all without any UNION’s. And I had never used UNPIVOT. So thanks for that one also. Appreciate your help with this. Margaret
No problem Margaret, it was a good challenge for me! I haven’t used UNPIVOT much before so it was a good example of using it. Glad it helps!
Hi Ben! Your explanations are really helpfull but i still can’t make work this query. It has a case inside another case, but the second case is being ignored and i don’t know why.
SELECT NUMEROLINEA,
FECHAACTIVACION AS ALTA,
ESTADOPROVISIONAMIENTO AS ESTADO,
NOMBRE,
APELLIDO,
NUMERODOCUMENTO AS DNI,
CALLENOMBRE AS CALLE,
CIUDADNOMBRE AS CIUDAD,
CASE NUMEROTELEFONO
WHEN NULL THEN
CASE NUMEROMOVIL
WHEN NULL THEN NUMEROTELEFONOCASA
ELSE NUMEROMOVIL END
ELSE NUMEROTELEFONO
END AS TELEFONO,
PROVINCIA
FROM A001470.PRODUCTOADQUIRIDO PA
INNER JOIN A001470.CUENTAFACTURACION CF
ON PA.IDCUENTAFACTURACION = CF.IDCUENTAFACTURACION
INNER JOIN A001470.INDIVIDUOCUENTAFACTURACION ICF
ON CF.IDCUENTAFACTURACION = ICF.IDCUENTAFACTURACION
INNER JOIN A001470.INDIVIDUO I ON ICF.IDINDIVIDUO = I.IDINDIVIDUO
INNER JOIN A001470.INDIVIDUOCUENTACLIENTE ICC
ON I.IDINDIVIDUO = ICC.IDINDIVIDUO
INNER JOIN A001470.DIRECCION D
ON ICC.IDCUENTACLIENTE = D.IDCUENTACLIENTE
WHERE NUMEROLINEA = ‘3584309290’
Hi Juan,
Thanks for the comment. That’s strange the second CASE is being ignored.
Could you test that the values in NUMEROTELEFONO are actually NULL?
To do this, you can replace your CASE statement with:
CASE NUMEROTELEFONO
WHEN NULL THEN ‘value is null’
ELSE NUMEROTELEFONO
END AS TELEFONO
Or, if you’re just testing for NULL values, you could use COALESCE, which returns the first non-NULL expression in the list:
COALESCE(NUMEROTELEFONO, NUMEROMOVIL, NUMEROTELEFONOCASA) AS TELEFONO
You can read more about COALESCE here.
Hi Ben,
can’t i use case within case like below, it says column does not exsist?
select
count(distinct(vid||active_session)),
(CASE WHEN (( current_page_url ilike ‘%optus.com.au/shop/broadband%’ OR
current_page_url ilike ‘%optus.com.au/shop/home-phone%’ OR
current_page_url ilike ‘%optus.com.au/shop/bundles%’ OR
current_page_url ilike ‘%optus.com.au/shop/entertainment%’ OR
current_page_url ilike ‘%optus.com.au/shop/deals-bundles%’ OR
current_page_url ilike ‘%optus.com.au/for-you/entertainment%’ OR
current_page_url ilike ‘%optus.com.au/business/broadband%’ OR
current_page_url ilike ‘%addBundleToCart%’) AND
current_page_url not ilike ‘%prepaid/checkout%’) THEN
(CASE WHEN current_page_url ‘%optus.com.au/shop/broadband/nbn%’ THEN ‘Fixed_NBN’
WHEN current_page_url ‘%optus.com.au/shop/broadband/mobile-broadband%’ THEN ‘Fixed_MBB’
ELSE ‘Fixed_Others’ END)
END) as prod,
purchase_flag
from idm.OPTUS_JOINED_VIEW_V_3_6
where dt between ‘2018-06-15’ and ‘2018-07-17’
group by prod,purchase_flag
order by prod
Hi Abhi,
If that’s the message you’re getting, which column does it say does not exist?
Also, the keyword “ilike” should be “like” to use wildcard searches.
Ben
I think I am having the same issue…
the column that can’t be see is “prod”… so the question is, if I capture the results of a case statement using “as”, how do I use it in with the “group by” so the count is summarized by the results of the case… ? (in the example above, the case results are captured “as prod” )
Hi Sue,
If you want to use the alias (the “AS prod” part) in the GROUP BY, you can’t do this in the same query. This is because the aliases are assigned in the SELECT clause which is processed after the WHERE clause.
There is a way to do this though. You can use the SELECT with the CASE and all its clauses as a subquery, then in the outer query use the GROUP BY.
A simple example:
SELECT columns, prodFROM
(
SELECT columms,
CASE your_case_criteria AS prod
FROM table
WHERE criteria
)
GROUP BY prod;
The GROUP BY is outside the subquery so it should work.
Very Informative. Helped me tremendously.
hi Ben
my question is if you want to put even and odd value in different column then how can i write the query
You can probably write two CASE statements to display it:
SELECT
CASE WHEN MOD(yourcolumn, 2)=0 THEN yourcolumn ELSE null END AS evenvalue,
CASE WHEN MOD(yourcolumn, 2)=1 THEN yourcolumn ELSE null END AS oddvalue
FROM yourtable;
This will show even values in one column, odd values in another. I haven’t tested this query so you might need to tweak it if you get a syntax error.
Hello! Thank you very much for your effort on this topic.
I don’ t understand one thing: sometimes (and which are the conditions to be so?)CASE exits when first value/expresion is TRUE, and sometimes it goes through all values/expresions?!
Can you please clarify what determines that?
Is thatconnected with comparisson signs (=, ) or with CASE expresions types( SIMPLEvs.SEARCHED )?
Hi Miro,
Good question. The CASE statement should exit when it reaches the first TRUE condition. This happens for both Simple and Searched expressions.
The only time I can think of where the CASE statement runs through each condition is if the first condition is false.
It’s like a series of IF ELSE. Only one condition can be true.
Hope that answers your question!
Thanks,
Ben
Hi, if I change your Simple CASE Statement example from above as followed:
SELECT
first_name, last_name, country,
CASE country
WHEN ‘USA’ THEN ‘1’
WHEN ‘Canada’ THEN ‘2’
WHEN ‘UK’ THEN ‘3’
WHEN ‘France’ THEN ‘Europe’
ELSE ‘Unknown’
END Continent
FROM customers
ORDER BY first_name, last_name;
then the so called the column alias Continent is truncated to Con. Is there a possibility to format the column alias?
Thank you.
Hi Claudia, are you running this on SQL*Plus? SQL*Plus and some IDEs may truncate the column heading to be the widest value. Which IDE are you using?
I have a nested Case statement within a where clause with multiple “whens” that errors on the first case.
INSERT INTO [PERMIL_STATUSES] (PERMIL_STATUSES.POS, PER_MILITARY_ID, PERMIL_MIL_STATUS, PERMIL_BRANCH, PERMIL_STATUS_START_DATE, PERMIL_STATUS_END_DATE,PERMIL_PRIMARY_FLAG)
SELECT MILITARY_ASSOC.POS, MILITARY_ASSOC.ID, MILITARY_STATUSES, MILITARY_BRANCHES, MILITARY_START_DATES, MILITARY_END_DATES
FROM MILITARY_ASSOC JOIN STPR_STATUSES
ON SUBSTRING(STPR_STATUSES.STUDENT_PROGRAMS_ID, 1, 7) = (MILITARY_ASSOC.ID)
WHERE STPR_STATUSES.POS=1 AND STPR_STATUS=’A’ AND NOT EXISTS
(SELECT *
(CASE – error (incorrect syntax near ‘CASE’, expecting ‘(‘ or SELECT)
WHEN MILITARY_STATUSES = ‘AAIR’,’AANG’,’AARMY’,’ACG’,’AMAR’,’ANAVY’,’ANG’
THEN ‘ACT’
WHEN MILITARY_STATUSES = ‘DODAF’, ‘DODAG’,’DODAR’,’DODCG’,’DODMA’,’DODNA’,’DODNG’
THEN ‘DOD’
WHEN MILITARY_STATUSES = ‘FAMAF’,’FAMAG’,’FAMAR’,’FAMCG’,’FAMMA’,’FAMNA’,’FAMNG’
THEN ‘DEP’
WHEN MILITARY_STATUSES = ‘RAIR’,’RARMY’,’RCG’,’RMAR’,’RNAVY’,’RNG’
THEN ‘RES’
WHEN MILITARY_STATUSES = ‘VAIR’,’VANG’,’VARM’,’VCG’,’VMAR’,’VNAVY’,’VNG’
THEN ‘HON’
ELSE NULL
END) PERMIL_MIL_STATUS
THEN
(CASE
WHEN MILITARY_STATUSES (‘AAIR’,’DODAF’,’FAMAF’,’RAIR’,’VAIR’)
THEN ‘AF’
WHEN MILITARY_STATUSES (‘AANG’,’DODAG’,’FAMAG’,’VANG’)
THEN ‘ANG’
WHEN MILITARY_STATUSES (‘AARMY’,’DODAR’,’FAMAR’,’RARMY’,’VARM’)
THEN ‘ARMY’
WHEN MILITARY_STATUSES (‘ACG’,’DODCG’,’FAMCG’,’RCG’,’VCG’)
THEN ‘CG’
WHEN MILITARY_STATUSES (‘AMAR’,’DODMA’,’FAMMA’,’RMAR’,’VMAR’)
THEN ‘M’
WHEN MILITARY_STATUSES (‘ANAVY’,’DODNA’,’FAMNA’,’RNAVY’,’VNAVY’)
THEN ‘NAVY’
WHEN MILITARY_STATUSES (‘ANG’,’DODNG’,’FAMNG’,’RNG’ ,’VNG’)
THEN ‘NG’
ELSE NULL
END) PERMIL_BRANCH
FROM PERMIL_STATUSES
WHERE PER_MILITARY_ID=MILITARY_ASSOC.ID
AND PERMIL_STATUSES.POS=1
AND PERMIL_PRIMARY_FLAG=’YES’);
Hi Deborah, I think this is because of the * character before the case. Select * means select all columns, but then you have a CASE statement.
Are you looking to select all columns from permil_statuses, as well as the result of the CASE statements? If so, it should be SELECT *, (CASE WHEN… Add the comma after *.
If you don’t want all columns and only want the results of the two CASE statements, then you can remove the *. SELECT (CASE WHEN …
Hope that helps!
I need to use case statement like below written ,Can someone help me in this ?
from
txn_logs tl,
t_sm_service_master sm,
t_wm_wallet_info wi, t_um_entity_detail ued
WHERE tl.service_id = sm.service_txn_id
and t1.entity_id = ued.entity_id
And tl.entity_id = wi.entity_id
and wi.wallet_type = 1
and Case
when ued.user_type in (85,73,74) then t2.amt_type in (‘TXN_AMT’,’COMM’) else t2.amt_type =’TXN_AMT’ end case
Write a query to display EMPLOYEES having ID 101,102,103 as per the
below order: 1. 101, 2. 103, 3. 102 (Hint: Union Operator / Case Statement)
Hi BEN,
i have employee table with column id, name, dept, salary
so i want sal which has greater than avg(sal) ,if sal >avg(sal) then give flag ‘Y’ other wise ‘N’?
COLUMN1 COLUMN2 COLUMN3
30 30 30
10 20 10
20 30 10
15 16 21
34 89 100
121 23 11
q1>if value of all three col is same for a particular row then show excellent, if value of
two is same then very good, if value of all three column are different then good
–>There are thousand of record in this table