Skip to Main Content
  • Questions

  • changing where clause using case statement

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question, H.

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

Last updated: January 17, 2020 - 8:52 am UTC

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 Chris 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;
/

Rating

  (4 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Excellent

H, January 15, 2020 - 7:35 am UTC

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

A reader, January 15, 2020 - 1:08 pm UTC


on OR Expansions

Rajeshwaran, Jeyabal, January 16, 2020 - 10:14 am UTC

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

Chuck Jolley, January 16, 2020 - 6:16 pm UTC

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.