Oracle NVL function

I have an oracle database that I am retrieving data with Access.  One of the columns I am retrieving can be null.  I need to interogate the column for certain values.  However, I need to convert the nulls to some value so they are included in the comparision.

In SQL, this is what I want to do:

select PROD_ID from table1 where nvl(PROD_ID,'00') not in ('08','09')

How can I do this in Access?
gwjAsked:
Who is Participating?
 
nico5038Connect With a Mentor Commented:
I guess NZ(PROD_ID,'00') is the function you're looking for.

Nic;o)
0
 
OtanaCommented:
I'm not sure, but you might want to try isnull(Field,Value)
0
 
KokaCommented:
Well, from Access point of view Nico is right and as the question was about NVL equivalent in Access he does deserve all those points :) , but I doubt it's was a good question - I think you are not handling Oracle backend in a proper way. Your query will retrieve all records from Oracle and will filter them locally - you loose all that client-server benefits. Better keep your NVL syntax and use pass-through query, then all the filtering job will be done by Oracle.

Good luck
0
 
oscb7013Commented:
select PROD_ID from table1
where PROD_ID is not null
and PROD_ID not in ('08','09')
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.