Select on a null, with multiple tables

Fri Feb 12 13:59:44 CST 2010

I keep the nulls also, I just don’t let Powerhouse know they are there.

BTW, I never allow nulls in the primary keys….that makes PH go weird.

I should have included a sample of the layout for you to see what I meant. Sorry

Create table Users

      (

       UserId Char(8) Default ' ' Not Null,

       UserName Char(40) Default ' ' Not Null,

       SecurityClass Char(8),

       LanguageId Char(2),

       UserDepartmentId Char(8),

…

      );

From: DAN H LAMBSHEAD [mailto:danhl at rogers.com] 
Sent: February 12, 2010 2:00 PM
To: Ken at Langendock.com; Dan Lambshead; powerh-l at lists.sowder.com
Subject: Re: [Bulk] Select on a null, with multiple tables

Hi Ken,

  Thanks. We are toughing it out, to handle null values for some fields where it makes sense, for our Oracle conversion of Powerhouse. Though it requires some extra coding, most of the time it works OK. This time it doesn't for some reason. 

  We figured to keep the nulls to make it more standardized  for any future language we might convert the Powerhouse to some day, So that can be useful, even more later on.

Dan 

  _____  

From: Ken Langendock <ken.langendock at rogers.com>
To: Dan Lambshead <dlambshe at milk.org>; powerh-l at lists.sowder.com
Sent: Thu, February 11, 2010 3:18:55 PM
Subject: RE: [Bulk] Select on a null, with multiple tables

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

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20100212/bedb62c0/attachment-0001.htm 