Home » SQL & PL/SQL » SQL & PL/SQL » case or if clause in where statement
case or if clause in where statement [message #203563] Wed, 15 November 2006 09:30 Go to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Hello,

I have a macro variable (&var) which is an integer or null. When I run the code below, I get an error if the &var is null.
How can I solve this issue? Is there an "if" or "where" clause which I can perform in the where statement? How?

select *
from tab
where P1 = &var
      and P2 = 5


Thanks
Stefan
Re: case or if clause in where statement [message #203568 is a reply to message #203563] Wed, 15 November 2006 09:50 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I think we need to step back a level. What are you running this from ? Forms ? SQL*Plus ? MS Access ?
Re: case or if clause in where statement [message #203569 is a reply to message #203563] Wed, 15 November 2006 09:54 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
I'm running this in SAS, the statement is then performed by Oracle as pass through.

The issue is that I want to execute the "P1 = &var" only if the variable is not null to prevent an error.

Any clue?

Thanks
Stefan
Re: case or if clause in where statement [message #203571 is a reply to message #203563] Wed, 15 November 2006 09:57 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Does the error you get start with "ORA-" ? If so, it is a genuine Oracle error. If not, it is probably coming from SAS and therefore nothing to do with Oracle.
Re: case or if clause in where statement [message #203573 is a reply to message #203563] Wed, 15 November 2006 10:10 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
yes, the error comes from oracle, because if the variable &var is missing in SAS, then &var is "."

So, the statement is passed through as:

select *
from tab
where P1 = .
      and P2 = 5


which obviously does not work in oracle. If &var is missing, then I just want to run this statement:

select *
from tab
where P2 = 5


How can I do this within the select statement?
Re: case or if clause in where statement [message #203576 is a reply to message #203563] Wed, 15 November 2006 10:13 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Passing a NULL or an empty string to Oracle doesn't normally cause an error, unless perhaps you are comparing a numeric value to an empty string. What is the exact Oracle error that you are getting ?
Re: case or if clause in where statement [message #203580 is a reply to message #203563] Wed, 15 November 2006 10:21 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
If I run

select *
from tab
where P1 = .
      and P2 = 5


then I get "ORA-00936: missing expression."


If I run

select *
from tab
where P1 is null
      and P2 = 5


then it works fine.

Re: case or if clause in where statement [message #203583 is a reply to message #203563] Wed, 15 November 2006 10:30 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
That means it is formulating the statement dynamically and you are ending up with:

select *
from tab
where P1 = 
      and P2 = 5


This wasn't clear from the earlier post, where I thought your "." represented a NULL value or empty string.

I don't think you can do anything at the Oracle end to deal with this situation. If nothing is passed in for the predicate value, then Oracle can't parse the statement properly. You will have to find some way of initialising the variable to NULL at the SAS end. Is it actually using a bind variable or just concatenating a hard-coded value into the SQL ?
Re: case or if clause in where statement [message #203602 is a reply to message #203563] Wed, 15 November 2006 13:59 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
select *
from tab
where ("&var" is null or P1 = "&var")
and P2 = 5
Previous Topic: Propagate Exception between procedure and function
Next Topic: joining huge tables
Goto Forum:
  


Current Time: Sat Dec 10 16:29:15 CST 2016

Total time taken to generate the page: 0.06296 seconds