using nvl to conquer is null vs. = value in where clause? [message #371033] |
Tue, 15 August 2000 10:44 |
Kelly Harrelson
Messages: 2 Registered: August 2000
|
Junior Member |
|
|
We have this scenario:
select somefield from sometable where somefield2 is null;
vs.
select somefield from sometable where somefield2 = somevalue;
what we want to do is :
select somefield from sometable where somefield2 = ?;
where the ? is our value (java)
Will nvl help us do that and how? If not, are there other options?
Thanks,
Kelly
|
|
|
|
Re: using nvl to conquer is null vs. = value in where clause? [message #371050 is a reply to message #371033] |
Thu, 17 August 2000 13:53 |
Andrew again...
Messages: 270 Registered: July 2000
|
Senior Member |
|
|
One way to do it is as follows:
select somefield from sometable where 'X'||somefield2 = 'X'||somevalue
Note that this knocks out the use of the index on somefield2, but if the table is small or the query runs infrequently then it's fine.
This has the advantage of simplifying the coding and is useful for queries where there are several possible search criteria which are often null. See this variation using "like".
Select * from mytab
where 'X'||col1 like 'X'||my_var1|'%'
and 'X'||col2 like 'X'||my_var2|'%'
and 'X'||col3 like 'X'||my_var3|'%'
Any or all of the my_var can be null. Using the like clause eliminates the use of indexes on that column even without the concat.
|
|
|
|
|