We have seen working of Oracle Decode processing in the previous post
Now let us see Oracle Case statement processing
Case statement in Oracle
It is similar to the Decode statement. Databases before Oracle 8.1.6 had only the DECODE function. CASE was introduced in Oracle 8.1.6 as a standard, more meaningful, and more powerful function.
Everything DECODE can do, CASE can also. There is a lot else CASE can do though, which DECODE cannot. We’ll go through detailed examples in this article
Let’s start with the Case statement Syntax
CASE [expression] when condition_1 then value_1 when condition_2 then value_2 when condition_2 then value_2 …. else value_n end
expression is optional
We can divide the Case statement into two categories Simple Case statement and Searchable case statement
Simple case statement is just like Decode function.
Example with Simple CASE statement
select case region when ‘N’ then ’North’ when ‘S’ then ’South’ when ‘E’ then ’East’, when ‘W’ then ’West’ else ‘UNKNOWN’ end from customer;
Searchable Case statement are case statement where we specify a condition or predicate (case statement in oracle with multiple conditions)
SQL> select emp_name , case when Salary < 10000 then 'Junior Level' when (Salary >=10000 and Salary < 50000) then 'Middle Level' when (Salary >= 50000 and Salary < 100000) then 'Senior Level' else 'Managers' end Emp_level from employee_info where rownum < 5; EMP_NAME EMP_LEVEL ---------- --------- JOHN Junior Level DON Senior Level BOB Manager BILL Middle Level
Nested Oracle Case statement
This is case statement within the case statement
SQL> select emp_name , case when Salary < 10000 then 'Junior Level' when (Salary >=10000 and Salary < 50000) then 'Middle Level' when (Salary >= 50000 and Salary < 100000) then 'Senior Level' else (Case when grade ='20' then 'Vice President' when grade='21' then 'Senior Vice President' else 'Manager' End) end Emp_level from employee_info where rownum < 5;
Important points about Simple and searchable Case statement
(1) The searched CASE evaluates the conditions independently under each of the “when” options. With this structure, far more complex conditions can be implemented with a searched CASE than a simple CASE.
(2) A searched CASE can combine multiple tests using several columns, comparisons, and AND/OR operators.
(3) Both simple and searched CASE constructs, the conditions are evaluated sequentially from top to bottom, and execution exits after the first match are found. So, suppose more than one condition is true, only the first action is considered.
(4) Oracle Database uses short-circuit evaluation. That is, for a simple CASE expression, the database evaluates each comparison_expr value only before comparing it to expr, rather than evaluating all comparison_expr values before comparing any of them with expr. Consequently, Oracle never evaluates a comparison_expr if a previous comparison_expr is equal to expr. For a searched CASE expression, the database evaluates each condition to determine whether it is true, and never evaluates a condition if the previous condition was true
Now lets see the difference between Case and Decode statement
(1) DECODE can work with only scalar values but CASE can work with logical operators, predicates, and searchable subqueries.
We know that decode can work with scalar values. We cannot use it for Logical operators. We have to convert it into scalar values to make use of that.
Case makes the whole process easier. We can easily use logical operator in the Case statement
SQL> select city , case when population < 100000 then 'Tier I' when (population >=100000 and population < 200000) then 'Tier II' when (population >= 200000 and population < 300000) then 'Tier III' else 'TIER IV' end City_Tier from city_info where rownum < 5; CITY CITY_TIER ---------- --------- XYX TIER I XYZ TIER II XZW TIER II
The above is called searchable Case statements
(2) CASE can work as a PL/SQL construct but DECODE is used only in SQL statements.CASE can be used as a parameter of a function/procedure.
Example
DECLARE V_x VARCHAR2(10) := 'A'; V_y VARCHAR2(10); BEGIN V_y := CASE V_x WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Good' WHEN 'C' Then 'Average' ELSE 'Poor' END; DBMS_OUTPUT.PUT_LINE( 'Grade V_x is '||V_y||'.' ); END; /
Grade V_x is Excellent.
(3) CASE expects datatype consistency, DECODE does not.
select case 5 when 1 then '1' 2 when '2' then '2' 3 else '5' 4 end 5 from dual; when '2' then '2' * ERROR at line 2: ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
(4) CASE complies with ANSI SQL. DECODE is proprietary to Oracle.
(5) CASE executes faster in the optimizer than does DECODE.
(6) CASE is a statement while DECODE is a function.
Related Articles
Oracle sql Tutorials : Listing of all the sql tutorial lessons which can be used to master sql and use in RDBMS (Oracle,MySql) data management and manipulation
Oracle interview questions: Check out this page for Top 49 Oracle Interview questions and answers : Basics , Oracle SQL to help you in interviews.
oracle PLSQL records : Check out this article on the working of oracle PLSQL records . Also, find out the various ways to define it and assign value to it
Oracle SQL developer tool: Check out this page for all the information on the Oracle sql developer tool,How to do Oracle sql developer download, how to install
oracle date functions : Check out this post for oracle date functions, oracle date difference in years,oracle date difference in days, oracle date difference in months.
https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm
Recommended Courses
Here is the nice Udemy Course for Oracle SQL
Oracle-Sql-Step-by-step : This course covers basic sql, joins, Creating Tables and modifying its structure, Create View, Union, Union -all and much other stuff. A great course and must-have course for SQL starter
The Complete Oracle SQL Certification Course : This is a good course for anybody who wants to be Job ready for SQL developer skills. A nice explained course
Oracle SQL Developer: Essentials, Tips and Tricks : Oracle Sql developer tool is being used by many developers. This course gives us tricks and lessons on how to effectively use it and become a productive sql developer
Oracle SQL Performance Tuning Masterclass 2020 : Performance tuning is one of the critical and most sought skills. This is a good course to learn about it and start doing sql performance tuning