OraFAQ Forum: Server Administration » using nvl to conquer is null vs. = value in where clause?

Home » RDBMS Server » Server Administration » using nvl to conquer is null vs. = value in where clause?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

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

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 ?

Report message to a moderator

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.

Report message to a moderator