Bind Variable with Null value 1

[gleeb]

Hi all,

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?

 

[jbenson001]

Are you sure the values being passed have a NULL? I belive and Integer if not assigned defaults to 0, and a string is empty, not null. So either of those will affect your query results. You would have to specificly assign a NULL. System.Convert.DBNULL I belive.

 

[gleeb]

You are correct. The variable in question is an empty string. If I use the empty string or assign NULL to the parameter value instead, I get the same result. No selection. The only time I get results is if I change the select statment to:

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

 

[MacLeod72]

The issue may be that in SQL, NULL = NULL does not evaluate as true, but NULL. If dts_lab_val_txt is the issue, for example, the SQL should read like the following to compare nulls:

SELECT
    dts_mbr_no 
FROM
    mbr_labs
WHERE
    dts_mbr_no = :mbr
    AND dts_lab_id = :lab
    AND ((dts_lab_val_txt = :result)
        OR (dts_lab_val_txt IS NULL AND :result IS NULL))

Hope this helps!

--Rob