Great!
A reader, April 24, 2007 - 1:28 pm UTC
I just leanrt something new! ;-)
Way cool, as always!
Duke Ganote, April 24, 2007 - 4:42 pm UTC
WITH
logical_operators AS (
SELECT 'contains' as SLOGIC FROM DUAL UNION ALL
SELECT 'equals' as SLOGIC FROM DUAL UNION ALL
SELECT 'begins' as SLOGIC FROM DUAL
)
, sample_data AS
( SELECT 'A1234B' as partnum
, '1234' as sPartStrp
FROM dual )
select slogic
, partnum
, spartstrp
, CASE WHEN slogic = 'contains' THEN '%' END
|| sPartStrp ||
CASE WHEN slogic <> 'equals' THEN '%' END
AS searchstring
from sample_data , logical_operators
/
SLOGIC PARTNU SPAR SEARCH
-------- ------ ---- ------
contains A1234B 1234 %1234%
equals A1234B 1234 1234
begins A1234B 1234 1234%
WITH
logical_operators AS (
SELECT 'contains' as SLOGIC FROM DUAL UNION ALL
SELECT 'equals' as SLOGIC FROM DUAL UNION ALL
SELECT 'begins' as SLOGIC FROM DUAL
)
, sample_data AS
( SELECT 'A1234B' as partnum
, '1234' as sPartStrp
FROM dual )
, sample_searches AS
(
SELECT slogic
, partnum
, spartstrp
, CASE WHEN slogic = 'contains' THEN '%' END
|| sPartStrp ||
CASE WHEN slogic <> 'equals' THEN '%' END
AS searchstring
from sample_data , logical_operators
)
SELECT slogic
, partnum
, spartstrp
, searchstring
FROM sample_searches
where partnum LIKE searchstring
/
SLOGIC PARTNU SPAR SEARCH
-------- ------ ---- ------
contains A1234B 1234 %1234%
Always find the solution to my problems.
SB, November 04, 2010 - 3:07 pm UTC
Great
Thanks for all the support you provide to the users.
This was EXACTLY what I was looking for
Steve Dodson, June 09, 2011 - 2:31 pm UTC
I had spent some time searching for the answer to my problem before I came upon the answer here. Very, very informative and helpful. Excellent info!
A reader, October 12, 2011 - 6:02 am UTC
I am getting 'missing keyword' exception while running query.
select * from BAN_CHECKOUT_INFO where
(case
when (CHECK_OUT_TIME != trunc(SYSDATE) ) THEN (ID = 'pk725a')
END
October 12, 2011 - 7:15 am UTC
of course you are. That is just like:
select * from ban_checkout_info where column;
or more generically
select * from ban_checkout_info where expression;
where expression WHAT? what about the expression? You need to do something with it.
what do you want to do with that CASE output? You need to compare it to something.
A reader, October 12, 2011 - 6:13 am UTC
select * from CHECK where
(case
when (CHECK_TIME != trunc(SYSDATE) ) THEN (ID = 'myid')
END)
Hi Tom,
am getting "ORA-00907 missing right parenthesis" while running the above query. if the condition 'CHECK_TIME != trunc(SYSDATE)' is true then query should be like
"select * from CHECK where ID = 'myid'"
October 12, 2011 - 7:17 am UTC
what about when it is not true?
seems like:
where check_time != trunc(sysdate) AND id = 'myid';
is what you are looking for, I'm very confused by your use of CASE here - very confused. It is really hard to look at completely invalid syntax and try to discern what you were thinking when trying to use it.
conditional case
A reader, December 22, 2011 - 8:43 pm UTC
Gr8 Work
isha, February 02, 2012 - 5:42 am UTC
I was searching for this solution from a long time...
was fed up of writing the same select statement in different if conditions with a few changes in where condition..
This was very useful... and i have implemented it successfully.. :)
thanks
Annamalai, February 06, 2012 - 10:26 am UTC
Hi Tom,
iam getting the below error
SQL Error: ORA-00905: missing keyword
where
case
when (select max(last_update_date) from table1)>to_date('&p_date_from','DD-MON-YYYY')
then cancelled_date BETWEEN select max(last_update_date) from table1 and to_date('&p_date_from','DD-MON-YYYY')
when (select max(last_update_date) from table1)<to_date('&p_date_from','DD-MON-YYYY')
then cancelled_date BETWEEN to_date('&p_date_from','DD-MON-YYYY') and to_date('&p_date_to','DD-MON-YYYY')
end
and lookup_type='Type'
February 06, 2012 - 10:58 am UTC
sorry? what else do you want me to say?
did you read my answer at all? the syntax you are using is completely invalid.
where
case when slogic = 'begins' and partnum like sPartStrp||'%'
then 1
when slogic = 'contains' and partnum like '%'||spartStrp||'%'
then 1
when slogic = 'equals' and partnum = sPartStrp
then 1
when partnum like sPartStrp || '%'
then 1
else 0
end = 1
and p.stock_type = 1
they syntax of a case statement is:
case
when BOOLEAN_EXPRESSION
then SCALAR_RETURN_VALUE
...
ELSE SCALAR_RETURN_VALUE
end
You can use that case in a manner like this:
case
when BOOLEAN_EXPRESSION
then SCALAR_RETURN_VALUE
...
ELSE SCALAR_RETURN_VALUE
end = SCALAR_VALUE
Clear cut explanation!
Ashish, March 20, 2012 - 12:19 am UTC
Short and sweet explanation. Thanks!
End = 1 Clarification
Gabriel St James, April 04, 2012 - 4:27 pm UTC
I just wanted to share a thought in case I wasn't the only one that was thrown by the formatting. I find the case statement easier to interpret in the where clause with parenthesis, though it can be written either way.
where
(case when slogic = 'begins' and partnum like sPartStrp||'%'
then 1
when slogic = 'contains' and partnum like '%'||spartStrp||'%'
then 1
when slogic = 'equals' and partnum = sPartStrp
then 1
when partnum like sPartStrp || '%'
then 1
else 0
end) = 1
April 04, 2012 - 8:20 pm UTC
and many would like () around the conditions/expressions in the when/then bits as well. Especially if they mix AND and OR - I would pretty much demand it with AND/OR conditions...
how about multiple OR?
A reader, April 04, 2012 - 9:24 pm UTC
Hi tom,
How about below, which one is better in terms of readability and performance?
where
(
(slogic = 'begins' and partnum like sPartStrp||'%')
or
(slogic = 'contains' and partnum like '%'||spartStrp||'%')
or
(slogic = 'equals' and partnum = sPartStrp)
or
(partnum like sPartStrp || '%')
)
and p.stock_type = 1
April 06, 2012 - 9:41 am UTC
performance should be about the same in this case - readability is sometimes in the eye of the beholder.
I would have not used the CASE statement myself
A reader, April 06, 2012 - 10:39 am UTC
how about using "instr" function instead of "like"
instr(partnum, sPartStrp)!=0 and creating FBI on it?
April 06, 2012 - 2:07 pm UTC
how, demonstrate
remember, spartstr is a bind variable, not a database column.
and also, the condition is sometimes leading, sometimes equal, sometimes in the middle - a simple instr() != 0 wouldn't cut it.
A reader, April 10, 2012 - 3:57 am UTC
(1) "and also, the condition is sometimes leading, sometimes equal, sometimes in the middle - a simple instr() != 0 wouldn't cut it."
SQL> select instr('find me in the string', 'find') from dual;
INSTR('FINDMEINTHESTRING','FIND')
---------------------------------
1
SQL> ed
Wrote file afiedt.buf
1* select instr('find me in the string', 'me in') from dual
SQL> /
INSTR('FINDMEINTHESTRING','MEIN')
---------------------------------
6
SQL> ed
Wrote file afiedt.buf
1* select instr('find me in the string', 'string') from dual
SQL> /
INSTR('FINDMEINTHESTRING','STRING')
-----------------------------------
16
(2) so if its bind variable FBI will not used?
Thanks
April 10, 2012 - 7:47 am UTC
show us the index you would create to satisfy this please.
instr != 0 bind variables, you cannot do it.
do function based indexes work with bind variables in a query? Absolutely.
Unless they are part of the function you are indexing itself.
where instr( database_column, 'some constant' ) = :x
that could use a function based index on instr(datbase_column,'some constant')
where instr( database_column, :x ) = constant
that cannot. there is nothing for you to "index", :x is not bound in the create index and if it were - it would be some constant again, not variable.
and please remember, the goal was to support
a) search leading, that would be instr() = 1
b) search middle, that would be instr() > 0
c) search trailing, that would be instr() = length(orig_string)-length(search_string)
a simple instr() <> 0 does not cut it.
A reader, April 10, 2012 - 9:04 am UTC
Thanks for clarification!!
A reader, April 10, 2012 - 9:22 am UTC
just a quick clarification,
search middle, that would be instr() > 1 instead of >0
right?
April 11, 2012 - 7:43 am UTC
no, it would be >0
to be like '%xxx%' means it could start with, be embedded in or end with xxx.
ORA-00907:
Tsvetoslav, April 20, 2012 - 3:47 am UTC
AND ( Case
WHEN (CG.person_id is not null AND CG.insurer_id is null)
THEN (PO.man_id = CRS.man_id)
ELSE (PI1.man_id = CRS.man_id)
END )
April 20, 2012 - 4:11 pm UTC
huh?
no idea who you are
what you are trying to do
what your tables look like
anything.....
I learn something new
ichay, September 28, 2012 - 10:38 pm UTC
I just know now, about case on where clause.
So, the value 1 and 0 can be as a result of boolean true and false.
This is my "case" :
case
when (:p_outs = 1) and (purchasing_pkg.get_outstanding(a.prcode, b.itemcode, :p_comp, b.quantity) != 0) then 1
when (:p_outs = 0) then 1
else 0
end = 1
thank you! :)
Nice article..
Vijay, December 20, 2012 - 9:45 am UTC
This information is quite helpful. I was working on this for quite a while before discovering this..Thank You..
Conditional Set of Conditions
Amit, July 08, 2013 - 10:54 am UTC
Hi,
I am trying to put different set of conditions in where clause based on whether particular column is null or not null. Is this achievable? Looking at existing responses appears its possible but I am getting error PL/SQl: ORA-00905: missing keyword.
WHERE
CASE WHEN v_ACCOUNT_ID IS NOT NULL THEN
BP.ACCOUNT_REF_ID = v_ACCOUNT_ID
AND v_DATE = BP.DATA_AS_AT_DT
WHEN v_ACCOUNT_ID IS NULL THEN
BP.ACCOUNT_REF_ID = v_ACCOUNT_NO
AND BP.ACCOUNT_REF_SRCE_SYSTEM_CD = v_NAT_SRCE_SYSTEM_CD
AND v_DATE = BP.DATA_AS_AT_DT
END = 1
July 16, 2013 - 2:32 pm UTC
I don't know what you are thinking with the CASE statement - case is a statement that returns an expression like 'Hello world'. Case cannot return "sql" to be processed.
You would just use a where clause for this:
where
(
(v_account_id is not null
AND
bp.account_ref_id = v_account_id
and
v_date = bp.data_as_at_dt
)
OR
(v_account_id is null
and
bp.acocunt_ref_id = v_account_no
and
bp.account_ref_srce_system_cd = v_nat_srce_system_cd
and
v_date = bp_data_as_at_dt
)
)
Got it working
Amit, July 08, 2013 - 11:04 am UTC
So foolish of me... Below part worked for above request..
Thanks...
WHERE
CASE WHEN v_ACCOUNT_ID IS NOT NULL THEN
v_ACCOUNT_ID
WHEN v_ACCOUNT_ID IS NULL THEN
v_ACCOUNT_NO
END = BP.ACCOUNT_REF_ID
AND
CASE WHEN v_ACCOUNT_ID IS NULL THEN
v_NAT_SRCE_SYSTEM_CD
END = BP.ACCOUNT_REF_SRCE_SYSTEM_CD
AND v_DATE = BP.DATA_AS_AT_DT
July 16, 2013 - 2:32 pm UTC
see above, way more readable and better for the optimizer to optimize.
case expression
Richard, October 30, 2013 - 4:50 pm UTC
What's the difference between case statement and case expression in SQL and PL/SQL? I could not
find any difference by looking at the syntax. Thanks for your excellent service to oracle
community. I am eagerly waiting for your response.
Thanxxxxxxx TOM
SHIV, February 25, 2016 - 5:13 am UTC
Thanxxxxxxx TOM i did go through the code and i resolve the issue
Very useful
Rekha, October 24, 2016 - 11:09 pm UTC
Thank you so much
October 25, 2016 - 2:20 am UTC
glad we could help
Wonderful - Traditional Excellent Help from TOM
Khalid, April 19, 2018 - 6:20 am UTC
Grateful.
great stuff
A reader, November 01, 2019 - 4:59 pm UTC
exactly what I am looking for!
Exactly what I was looking for!
Harsha, April 01, 2020 - 5:29 pm UTC
Thank you! This is such a great resource! I had exactly the same question and the solution works beautifully.
April 02, 2020 - 3:25 am UTC
Glad we could help