[Bulk] Select on a null, with multiple tables

[Ken Langendock]

Curious, I could never get Nulls to work correctly in Powerhouse.

 

I set NULL VALUES NOT ALLOWED in the PDL and then in the table. 

 

I do a before row trigger to store nulls in the database as in:

 

CREATE OR REPLACE TRIGGER br_Users

  BEFORE INSERT OR UPDATE ON Users

  REFERENCING NEW AS NEW OLD AS OLD

  FOR EACH ROW DECLARE

  BEGIN

    :NEW.LanguageId := NULLIF(:NEW.LanguageId,' ');

    :NEW.SecurityClass := NULLIF(:NEW.SecurityClass,' ');

    :NEW.UserDepartmentId := NULLIF(:NEW.UserDepartmentId,' ');

  END;

/

 

That way the Foreign key constraint would not fire if the value was NULL.

Then I can select on " " instead of NULL in the powerhouse source code.

 

Ken

 

 

From: powerh-l-bounces+ken.langendock=rogers.com at lists.sowder.com
[mailto:powerh-l-bounces+ken.langendock=rogers.com at lists.sowder.com] On
Behalf Of Dan Lambshead
Sent: February 11, 2010 2:43 PM
To: powerh-l at lists.sowder.com
Subject: [Bulk] Select on a null, with multiple tables

 

Hello,

 

  I just noticed something strange converting to Oracle 8, using Powerhouse
8.40.D1, when selecting a null value in one table, where multiple tables are
involved. It doesn't seem to work ! Either I have to limit the SELECT to
just that table with the null, or code an additional  statement "SELECT
file IF field IS NULL"  for that table.   Or I can use a define setting it
to 0 when that field is null and use the define in the select.  So I have
the work arounds, but just curious what's going on ?

 

ie.

 

Access Oracle_table_1 &

  Link to Oracle_table_2 opt

 

Select if field of Oracle_table_1 = "Y" and &

  field of Oracle_table_2 IS NULL

 

Doesn't work, Will always return 0 records  !!

 

But the following will work and return some records

 

1) Select if field of Oracle_table_2 IS NULL

 

2) Select if field of Oracle_table_1 = "Y"

    Select Oracle_table_2 if field of Oracle_table_2 IS NULL

 

3) DEFINE D_FIELD  =  field of Oracle_table_2 &
        IF NOT field of Oracle_table_2 IS NULL  ELSE 0

    

    Select if field of Oracle_table_1 = "Y" and & 

     D_FIELD = 0

 

   (This will work for me since the field is actually a date type which
cannot be 0 in the first place

 

 

Thanks

 

Dan

    

 

 

 

 

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.733 / Virus Database: 271.1.1/2680 - Release Date: 02/11/10
02:35:00

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20100211/4ccd39ba/attachment.htm