Re: NULL in Master/Detail FORM 6.0

In article <iv7llzzxvc.fsf_at_bcstec.ca.boeing.com>, Candy Robinson <candy_at_bcstec.ca.boeing.com> wrote:
>
>I have a master-detail form that contains as the primary key in the master,
>a unique constraint around 3 items. This way, one of the 3 items may be
>NULL. The data in the underlying database is correct, but when I query the
>master, the detail shows up incorrectly for those records containing a NULL
>in one of the 3 items. It contains all details for all combinations that
>don't have the item NULL. How do I get the detail to work properly for NULL
>values in the master? Thanks for any help! --Candy

Due to the silly treatment of nulls in conditional expressions, it is not a good idea to create a table where one of the key lookup values can be null. However, you can probably make your form work if you enclose the fields that can be null inside an NVL function in the where clause:

NVL(Table_2.Key_Itm,' ') = NVL(:Blk1.Key_Item,' ')

I use a space between the quotes as the NVL literal.

Be careful with using any function in a where clause, though. Depending on the situation, it can cause Oracle to do a full-table scan.

Steve Cosner
http://members.aol.com/stevec5088 Received on Sun Sep 12 1999 - 22:28:35 CEST