Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rick.

Asked: April 24, 2007 - 12:23 pm UTC

Last updated: April 02, 2020 - 3:25 am UTC

Version: oracle 9i r2

Viewed 100K+ times! This question is

You Asked

Hi Tom,
I have a question and I don't know if this is possible or if i'm jsut doing something wrong because i get multiple errors like missing right paren, or missing keyword.
I want to use the CASE construct after a WHERE clause to build an expression. for example.

Create Procedure( aSRCHLOGIC IN Varchar, aPARTSTRP IN VarChar )

Declare
sLogic VARCHAR(100) := aSRCHLOGIC;
sPartStrp VARCHAR(100);

BEGIN

SELECT * FROM parts p
WHERE ( Case sLogic
WHEN (sLogic = 'begins') THEN (p.partnum LIKE sPartStrp||'%')
WHEN (sLogic = 'contains') THEN (p.partnum LIKE '%'||sPartStrp||'%')
WHEN (sLogic = 'equals') THEN (p.partnum = sPartStrp)
ELSE (p.partnum LIKE sPartStrp||'%')
END )
AND p.stock_type = 1


END;

Is this possible? Can i use the CASE construct in this way?
Thanks in advance
Rick

and Tom said...

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



just think of case as a function that returns a SQL type. In a predicate, you would always do

where f(x) <operator> expression


case is the f(x)
operator is = in this case
express is 1

where case .... end = 1



Rating

  (27 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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
Tom Kyte
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'"

Tom Kyte
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'

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


Tom Kyte
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
Tom Kyte
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?
Tom Kyte
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


Tom Kyte
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?

Tom Kyte
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 )
Tom Kyte
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
Tom Kyte
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
Tom Kyte
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
Connor McDonald
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.
Connor McDonald
April 02, 2020 - 3:25 am UTC

Glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library