Re: WHERE clause for NULL and non-NULL values

In article <4192_at_ncrsoph.Sophia.NCR.COM>, sstasuke_at_ncrsoph.Sophia.NCR.COM (Steve Stasukewicz) writes:
>
> 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

Hi Steve,

Forget the WHERE - Clause in this case and create a PRE-QUERY Trigger like:

#EXEMACRO CASE :prevblock.field1 IS
 WHEN '' THEN COPY '# IS NULL' INTO :table_block.column;  WHEN OTHERS THEN COPY :prevblock.field1 INTO :table_block.column; END CASE; You can also write to the WHERE-Clause:

WHERE (table.column = :prevblock.field1 OR

       (:prevblock.field1 IS NULL AND table.column IS NULL))

But in this case an index on field "table.column" will not be used.  

The PRE-QUERY Trigger copies the value of field prevblock.field1 into the basetable screen field table.column if it is not null (And an Index will be used.). If it is null it extents the where clause to "WHERE table.column IS NULL" (Full table scan).

kind regards

Mike

                                                                          ^
 Federal Ministry of Science and Research                               B | M
<-------------------------------------------------------------------------+---->
 Computer Center                                                        W | F
                                                                          |
 Klaus-Michael Hatzinger            mail: hatzinger_m_at_bmwf1f.bmwf.gv.at   |
 Bankgasse 1/209                   phone: 0043-222-53120/5188             |
 1014 Vienna, Austria                fax: 0043-222-53120/5155             V
================================================================================