Skip to Main Content
  • Questions
  • changing where clause using case statement

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, H.

Asked: January 14, 2020 - 9:22 am UTC

Answered by: Chris Saxon - Last updated: January 17, 2020 - 8:52 am UTC

Category: SQL - Version: 10g

Viewed 1000+ times

You Asked

Hello Tom

Is it possible to change the where condition (using case statement) based on certain variable?

For example
var T varchar2(1)
exec :T := 'E';
var E number;
exec :E := 7788;
var N varchar2(20)
exec :N := 'MILLER';

select empno, ename
from emp
where
-- how to use case statement to vary the condition based on :T
-- if :T = 'E' then the condition should be
where empno = :E
-- and if :T = 'N' then the condition should be 
where ename like :N

and we said...

Wellll, you can kinda do this by having a case expression on either side of your condition.

For example:

var T varchar2(1)
exec :T := 'E';
var E number;
exec :E := 100;
var N varchar2(20)
exec :N := 'Neena';

select employee_id, first_name 
from   hr.employees
where  case
  when :T = 'E' then to_char ( :E )
  when :T = 'N' then :N
end = case
  when :T = 'E' then to_char ( employee_id )
  when :T = 'N' then first_name
end;

EMPLOYEE_ID    FIRST_NAME   
           100 Steven        

exec :T := 'N';

select employee_id, first_name 
from   hr.employees
where  case
  when :T = 'E' then to_char ( :E )
  when :T = 'N' then :N
end = case
  when :T = 'E' then to_char ( employee_id )
  when :T = 'N' then first_name
end;

EMPLOYEE_ID    FIRST_NAME   
           101 Neena   


But this is a TERRIBLE idea!

Using expressions like this destroys the optimizer's ability to use indexes. So these queries will be slow.

It's far better to inspect the value of T first, then run the appropriate (static) SQL statement:

begin
  if :T = 'E' then

    select ...
    into   ...
    from   hr.employees
    where  employee_id = :E; 

  elsif :T = 'N' then

    select ...
    into   ...
    from   hr.employees
    where  first_name = :N;

  end if; 
end;
/

and you rated our response

  (4 ratings)

Reviews

Excellent

January 15, 2020 - 7:35 am UTC

Reviewer: H

That was great. You have shown a great way to use case on either side of the operator. In the original question, I used "=" in first case and "like" in second case, is it possible to dynamically change relational operator too?

-- if :T = 'E' then the condition should be
where empno = :E
-- and if :T = 'N' then the condition should be 
where ename like :N

Chris Saxon

Followup  

January 15, 2020 - 12:08 pm UTC

I think you missed the part where I said:

But this is a TERRIBLE idea!

;)

But no, you can't switch operator like this. Remember LIKE with no wildcards is the same as =.

January 15, 2020 - 1:08 pm UTC

Reviewer: A reader


on OR Expansions

January 16, 2020 - 10:14 am UTC

Reviewer: Rajeshwaran, Jeyabal

How about an OR clause like this?

that would be index friendly and also opens up OR Expansion optimization.

demo@PDB1> create table t as
  2  select object_id as empno,
  3     object_name as ename,
  4     created as hiredate,
  5     data_object_id as comm
  6  from all_objects;

Table created.

demo@PDB1> create index t_empno_idx on t(empno);

Index created.

demo@PDB1> create index t_ename_idx on t(ename);

Index created.

demo@PDB1> variable e number
demo@PDB1> variable n varchar2(30)
demo@PDB1> variable t varchar2(1)
demo@PDB1> exec :t :='E'; :e := 55;

PL/SQL procedure successfully completed.

demo@PDB1> set autotrace traceonly explain statistics
demo@PDB1> select *
  2  from t
  3  where ( empno = :e and :t = 'E' )
  4  or ( ename =:n and :t = 'N') ;


Execution Plan
----------------------------------------------------------
Plan hash value: 2111273234

---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |     1 |    50 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T           |     1 |    50 |     4   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |             |       |       |            |          |
|   3 |    BITMAP OR                        |             |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS   |             |       |       |            |          |
|*  5 |      INDEX RANGE SCAN               | T_ENAME_IDX |       |       |     3   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS   |             |       |       |            |          |
|*  7 |      INDEX RANGE SCAN               | T_EMPNO_IDX |       |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME"=:N AND :T='N' OR :T='E' AND "EMPNO"=TO_NUMBER(:E))
   5 - access("ENAME"=:N)
   7 - access("EMPNO"=TO_NUMBER(:E))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          1  physical reads
          0  redo size
        761  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

demo@PDB1> exec :t :='N'; :n := 'Tom';

PL/SQL procedure successfully completed.

demo@PDB1> select *
  2  from t
  3  where ( empno = :e and :t = 'E' )
  4  or ( ename =:n and :t = 'N') ;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2111273234

---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |     1 |    50 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T           |     1 |    50 |     4   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |             |       |       |            |          |
|   3 |    BITMAP OR                        |             |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS   |             |       |       |            |          |
|*  5 |      INDEX RANGE SCAN               | T_ENAME_IDX |       |       |     3   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS   |             |       |       |            |          |
|*  7 |      INDEX RANGE SCAN               | T_EMPNO_IDX |       |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME"=:N AND :T='N' OR :T='E' AND "EMPNO"=TO_NUMBER(:E))
   5 - access("ENAME"=:N)
   7 - access("EMPNO"=TO_NUMBER(:E))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        597  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

demo@PDB1>


Based on the value set on the bind variable T - appropriate portion of the plan will execute.
Chris Saxon

Followup  

January 16, 2020 - 2:08 pm UTC

Good suggestion. The optimizer's still likely to fall back to a full table scan if you have more than a couple of columns to choose from though.

January 16, 2020 - 6:16 pm UTC

Reviewer: Chuck Jolley from OKC, OK USA

Why not just union all?
Won't the optimizer simply ignore the clauses where :t isn't appropriate because of them being impossible to make true?

select *
  from t
where empno = :e and :t = 'E' 
union all
select *
 from t
where ename =:n and :t = 'N';

Chris Saxon

Followup  

January 17, 2020 - 8:52 am UTC

Another good suggestion!

It might get fiddly if there are standard predicates to apply every time. Or you need to join the table. But definitely better than "double case" :)

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.