FB

SQL CASE Statement Explained with Examples

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_name

The 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'
END

All 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:

29 thoughts on “SQL CASE Statement Explained with Examples”

  1. 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”?

    1. Ben (Complete IT Professional)

      Hi Gregg, yes you can use a CASE statement in both the SELECT and WHERE clauses if you wanted to.

      1. 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.

        1. 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.

  2. 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

    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

      1. 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

        1. 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!

  3. 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’

    1. 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.

  4. 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

    1. 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

      1. 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” )

        1. 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, prod
          FROM
          (
          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.

    1. 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.

  5. 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 )?

    1. 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

  6. 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.

    1. 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?

  7. 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’);

    1. 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!

  8. 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

  9. BHUSHAN RAMESH ALONI

    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)

  10. Avinash Arvind Mane

    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’?

  11. 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

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.