Bind Variable with Null value

[gleeb]

Hi all, don't know if this is the correct forum, but here goes .

I am developing in vb.NET using ODP and Oracle 10g. I've set up a SELECT statement using bind variables, and up until now it hasn't been a problem. Example:

SELECT dts_mbr_no FROM mbr_labs
WHERE dts_mbr_no = :mbr AND dts_lab_id = :lab AND dts_lab_val_txt = :result

The problem occurs when one of the variable values is NULL. There are entries in the table that have a NULL value, but they are not getting selected.

I could create a different SELECT statement if the input variables are null, but that seems cheesy. Is there a nicer way to do this?

 

[BJCooperIT]

SELECT dts_mbr_no 
FROM   mbr_labs 
WHERE  dts_mbr_no = nvl(:mbr,dts_mbr_no)
AND    dts_lab_id = nvl(:lab,dts_lab_id)
AND    dts_lab_val_txt = nvl(:result,dts_lab_val_txt)

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com