• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1366
  • Last Modified:

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?
0
gwj
Asked:
gwj
1 Solution
 
OtanaCommented:
I'm not sure, but you might want to try isnull(Field,Value)
0
 
nico5038Commented:
I guess NZ(PROD_ID,'00') is the function you're looking for.

Nic;o)
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now