Select on a null, with multiple tables

[DAN H LAMBSHEAD]

Hi Ken,

  Yes, if I had looked more closely at your row trigger, I could have figured that this was what you were up to. That makes more sense now.  And we too have not allowed nulls in the primary key - I didn't even think that it was possible to allow nulls there, if it is unique anyway, which a null sort of violates.

Dan




________________________________
From: Ken Langendock <ken.langendock at rogers.com>
To: DAN H LAMBSHEAD <danhl at rogers.com>; Dan Lambshead <dlambshe at milk.org>; powerh-l at lists.sowder.com
Sent: Fri, February 12, 2010 2:59:44 PM
Subject: RE: Select on a null, with multiple tables


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/e2dbfc6d/attachment.htm