Select on Null Values

[kwil38]

I'm creating a .asp page in which a customer will enter his address info (house number, street direction, street name, street type, post direction, apartment, and zip). The page will then attempt to verify whether or not the address exists in a view via a Select statement on all of these fields.

The problem I'm running into is that not everyone will be entering information for all of the fields. For example, 123 Main ST, 71230 would only have a house number(123), street name (Main), street type (ST), and zip (71230). When this occurs, the Select statement won't return any rows since it's still searching on all fields and some of the fields contain Null values.

Is there any way around this other than to create a different Select statment for each scenerio?

 

[lewisp]

You can code a WHERE like this

 SELECT *
FROM    table
WHERE   Nvl(address1,'!"£$^&*-') = Nvl(:user_entered_address1,'!"£$^&*-')
AND     Nvl(address2,'!"£$^&*-') = Nvl(:user_entered_address2,'!"£$^&*-')
.
.
etc

...but I'm not sure how performance will be affected by this.

 

[engineer2100]

Yes, please let me know of the performance. We are actually about to run a similar SQL for finding delta - Updated - rows....

thanks
Engi

 

[kwil38]

I ended up using an OR statement...

Select * from view where house = enteredvalue1 and (street_direction = enteredvalue2 OR street_direction IS NULL)......

Don't know why I didn't think of that before. Guess I had been looking at it for too long to come up with the simple answer!

Thanks for your response lewisp!

 

[kwil38]

engineer2100 - Using the "OR" statement, I didn't notice any downgrade in performace.

 

[sem]

It depends on wheather you have a bitmap index on that field: it may be picked up with OR but not with NVL. In other cases both statements are about the same.

Regards, Dima

http://seminihin.narod.ru