case when in where clause oracle 11g
It may be another way of doing this
I would like to be able to evaluate on a flag that is sent by an interface to determine to filter by a date or not to filter by a date by using the case when
select emp_table
from table
where emp_assignment_status = 'ACTIVE'
and emp_dept like '%MTN%'
and
-- determine if I should filter by date or not
case in_flag -- parameter valuse passed from a java inteface
when 'on' then
emp_eoas > to_date('01/01/2012','MM/D
when 'off' then
-- do not filter by date
end
I would like to be able to evaluate on a flag that is sent by an interface to determine to filter by a date or not to filter by a date by using the case when
select emp_table
from table
where emp_assignment_status = 'ACTIVE'
and emp_dept like '%MTN%'
and
-- determine if I should filter by date or not
case in_flag -- parameter valuse passed from a java inteface
when 'on' then
emp_eoas > to_date('01/01/2012','MM/D
when 'off' then
-- do not filter by date
end
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
You might also want a '>=' to account for the time portion of the date data type in Oracle.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
How would I modify to bring records that do not have a date value (null) when the in_flag is set to 'no'.
To pass the date value to a dynamic sql,
Would I define it like this: in_date in DATE
Would I insert in my seql statement like this:
select emp_table
from table
where emp_assignment_status = 'ACTIVE'
and emp_dept like '%MTN%'
and emp_eoas > case in_flag when 'on' then in_date
else to_date('0001','YYYY') end
To pass the date value to a dynamic sql,
Would I define it like this: in_date in DATE
Would I insert in my seql statement like this:
select emp_table
from table
where emp_assignment_status = 'ACTIVE'
and emp_dept like '%MTN%'
and emp_eoas > case in_flag when 'on' then in_date
else to_date('0001','YYYY') end
I knew there was an 'easy' way. Just couldn't think of it.
note, SQL does short circuit boolean evaluation
so putting in_flag='off' means it won't even look at the column condition if flag is off.
this is not just an efficiency thing, it also affects the results...
this :
>>> and emp_eoas > case in_flag when 'on' then to_date('01/01/2012','MM/D
won't work if emp_eoas is nullable.
so putting in_flag='off' means it won't even look at the column condition if flag is off.
this is not just an efficiency thing, it also affects the results...
this :
>>> and emp_eoas > case in_flag when 'on' then to_date('01/01/2012','MM/D
won't work if emp_eoas is nullable.
ah, you already caught the null problem.
just use the OR, and skip CASE
just use the OR, and skip CASE
and nvl(emp_eaos,in_date + 1) > in_date
using NVL like that will most likely prevent use of any indexes on emp_eaos
also how does that handle the on/off parameter?
also how does that handle the on/off parameter?
ASKER
So which is more efficient is this one?
select emp_table
from table
where emp_assignment_status = 'ACTIVE'
and emp_dept like '%MTN%'
and ( in_flag='off'
or
emp_eoas > to_date('01/01/2012','MM/D
select emp_table
from table
where emp_assignment_status = 'ACTIVE'
and emp_dept like '%MTN%'
and ( in_flag='off'
or
emp_eoas > to_date('01/01/2012','MM/D
Generate the execution plan and see:
explain plan for
select emp_table
from table
where ...
select * from table(dbms_xplan.display);
explain plan for
select emp_table
from table
where ...
select * from table(dbms_xplan.display);
run them both and look at statistics, autotrace is easiest.
time them
I think the version I posted should be the most efficient, but test for yourself.
time them
I think the version I posted should be the most efficient, but test for yourself.
Oracle Database
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
