Home » SQL & PL/SQL » SQL & PL/SQL » optional condition in SQL
optional condition in SQL [message #390632] Mon, 09 March 2009 01:31 Go to next message
casttree
Messages: 83
Registered: August 2008
Member
I hope to add the optional condition when the input parameter is not NULL.

I tried the following query in JDBC statement, but hope to find the better ways

The JDBC query has 3 input parameters from Java function

col1 VARCHAR2
col2 NUMBER -- start id , value >= 0
col3 NUMBER -- end id , value >= 0

select * from table1
where 
col1 LIKE  NVL(?, '%')  -- if col1 input is null, no condition for col1
AND col2 < NVL(?, -1)   -- if col2 is null, no condition for col2
AND col3 > NVL(?, 500)  -- should I set MAX value as 2 ^38??  through the real value could much smaller



should we use case statement for this case? I tried to use case like below, but get some messgae ORA-00933: SQL command not properly ended

select * from table1
 CASE WHEN ?  != ''
     then 
         where col1 = ? 
 END
 



some other ways to do that?

[Updated on: Mon, 09 March 2009 01:42]

Report message to a moderator

Re: optional condition in SQL [message #390637 is a reply to message #390632] Mon, 09 March 2009 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If col2 >= 0, then "col2 < NVL(?, -1)" is equivalent to "col2 < ?". I think in this case you want to ignore the test.
The clearer (and I think best) solution is "(? is null or col2 < ?)".
You can also use "col2 < nvl(?,col2+1)" or "col2 < nvl(?,<a big number>)".

Same thing for other parameters.

Regards
Michel

Re: optional condition in SQL [message #390643 is a reply to message #390637] Mon, 09 March 2009 02:21 Go to previous messageGo to next message
casttree
Messages: 83
Registered: August 2008
Member
Michel Cadot wrote on Sun, 08 March 2009 23:56
If col2 >= 0, then "col2 < NVL(?, -1)" is equivalent to "col2 < ?". I think in this case you want to ignore the test.
The clearer (and I think best) solution is "(? is null or col2 < ?)".
You can also use "col2 < nvl(?,col2+1)" or "col2 < nvl(?,<a big number>)".

Same thing for other parameters.

Regards
Michel





Thanks, I think "col2 < nvl(?,col2+1)" solved my issue and it is better than my original col2 < nvl(? 500)

but I am not clear for

Quote:
" The clearer (and I think best) solution is "(? is null or col2 < ?)".




if I just put col2 < ? and ? could be null from UI. so the condition will be col2 < '', that is waht I hope to avoid. I hope when input is '', just ignore the condition for table1.col2 < ?

also I should mention col1 , col2, col3 are the columns of table1, not variable.

[Updated on: Mon, 09 March 2009 02:35]

Report message to a moderator

Re: optional condition in SQL [message #390647 is a reply to message #390643] Mon, 09 March 2009 02:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
COL1 < ?
will not be translated to
COL1 <
in case your bind variable is null. Instead, it will be translated to
COL1 < null

Michel offered you the correct solution in his cleared solution

[Updated on: Mon, 09 March 2009 02:38]

Report message to a moderator

Re: optional condition in SQL [message #390650 is a reply to message #390647] Mon, 09 March 2009 02:58 Go to previous messageGo to next message
casttree
Messages: 83
Registered: August 2008
Member
when I check the query, the binding variable of col2 is changed as col2 < '', but I understand Michel's soluation now, since is ? is null, (? is null or col2 < ? ) will be true as the first condition is already true.

However, after I put it in code, I found an issue with that soluion, because in (? is null or col2 < ? ) it used 2 input parameter , rather than 1 input parameter, the second input ? in col2 < ? got the next input in JDBC query.

I guess col2 < nvl(?, col2 +1 ) is good enough for me now.

Thanks,
Re: optional condition in SQL [message #390654 is a reply to message #390650] Mon, 09 March 2009 03:16 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
However, after I put it in code, I found an issue with that soluion, because in (? is null or col2 < ? ) it used 2 input parameter , rather than 1 input parameter, the second input ? in col2 < ? got the next input in JDBC query.

In this case you can change the query to something like:
with params as (select ? param1, ? param2, ? param3 from dual)
select ...
from ..., params
where ... and (param2 is null or col2 < param2) and ...

Regards
Michel
Previous Topic: How to find the Range
Next Topic: Instr Query
Goto Forum:
  


Current Time: Fri Dec 09 04:02:57 CST 2016

Total time taken to generate the page: 0.05767 seconds