WHERE clause for NULL and non-NULL values

From: Steve Stasukewicz <sstasuke_at_ncrsoph.Sophia.NCR.COM>
Date: 29 Nov 92 18:51:16 GMT
Message-ID: <4192_at_ncrsoph.Sophia.NCR.COM>

Once again, I need your help. I'm using ORACLE RDBMS V6, and SQL*Forms 2.3. I have a block that executes a query automatically when the block is entered. The block is a multi-record block that is scrollable if the query retrieves more than the 10 rows that display on the screen. The "ORDERING" option includes a WHERE clause like the following:

WHERE table.column = :prevblock.field1

Both table.column and :prevblock.field1 are defined as numbers, and :prevblock.field1 is user updatable.

If the user enters a number in the field, the query works OK. My problem is when the user leaves the field blank. In this case I want the query to retrieve those records that match the following WHERE clause :

WHERE table.column IS NULL

How can I code the WHERE clause to handle both NULL and non NULL values? I assume what it's doing now is trying to match table.column = '' if nothing is entered. This, I realized, is not the same as being NULL. :{ Thanks in advance for any suggestions.

Regards,

Steve

A special thanks to all those who replied either to the net or via email to my previous problem. ( Populating a multi-table block ). The POST_QUERY solution worked perfectly! Creating a VIEW and using that as the base table was also a good idea, but was not the optimal solution for us.

---

Steve.Stasukewicz_at_sophia.NCR.FR
NCR Europe Group ISS/ECS
Espaces De Sophia

25 Route de Lucioles                   Tel. (33) 92.94.45.12
06560 Valbonne FRANCE                  Fax. (33) 92.94.45.45

-- 

Received on Sun Nov 29 1992 - 19:51:16 CET