Home » RDBMS Server » Server Administration » using nvl to conquer is null vs. = value in where clause?
using nvl to conquer is null vs. = value in where clause? [message #371033] Tue, 15 August 2000 10:44 Go to next message
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 #371035 is a reply to message #371033] Tue, 15 August 2000 13:49 Go to previous messageGo to next message
Geraldo Viana de Paula Ju
Messages: 8
Registered: August 2000
Junior Member
My english isn't strong, but, try me understand !

You want select rows that match a null column and in addition a pattern value in the same column ?!?

So:
SELECT somefield
FROM sometable
WHERE (somefield2 is null
OR somefield2 = somevalue);

Is that the case ?
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 Go to previous messageGo to next message
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.
Re: using nvl to conquer is null vs. = value in where clause? [message #371051 is a reply to message #371035] Thu, 17 August 2000 14:20 Go to previous messageGo to next message
Kelly Harrelson
Messages: 2
Registered: August 2000
Junior Member
Yes. That's the case. What I have done that seems to be working is :
where nvl(somefield, defvalue) = nvl(somevalue, defvalue)
Re: using nvl to conquer is null vs. = value in where clause? [message #371058 is a reply to message #371051] Fri, 18 August 2000 19:55 Go to previous message
Andrew again...
Messages: 270
Registered: July 2000
Senior Member
NVL() is lighter on processing than the concatenation, so may actually be marginally faster - although a little more obscure.
Previous Topic: To_Date with Null
Next Topic: Re: Result Set from Procdeure (?)
Goto Forum:
  


Current Time: Fri Oct 30 02:11:20 CDT 2020