<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><DIV>Hi Ken,</DIV>
<DIV> </DIV>
<DIV> 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.</DIV>
<DIV> </DIV>
<DIV>Dan<BR></DIV>
<DIV style="FONT-FAMILY: times new roman, new york, times, serif; FONT-SIZE: 12pt"><BR>
<DIV style="FONT-FAMILY: times new roman, new york, times, serif; FONT-SIZE: 12pt"><FONT size=2 face=Tahoma>
<HR SIZE=1>
<B><SPAN style="FONT-WEIGHT: bold">From:</SPAN></B> Ken Langendock <ken.langendock@rogers.com><BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B> DAN H LAMBSHEAD <danhl@rogers.com>; Dan Lambshead <dlambshe@milk.org>; powerh-l@lists.sowder.com<BR><B><SPAN style="FONT-WEIGHT: bold">Sent:</SPAN></B> Fri, February 12, 2010 2:59:44 PM<BR><B><SPAN style="FONT-WEIGHT: bold">Subject:</SPAN></B> RE: Select on a null, with multiple tables<BR></FONT><BR>
<STYLE>
<!--
_filtered {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}
_filtered {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;font-family:"Times New Roman", "serif";}
a:link, span.MsoHyperlink
{color:blue;text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;text-decoration:underline;}
p
{margin-right:0cm;margin-left:0cm;font-size:12.0pt;font-family:"Times New Roman", "serif";}
p.msochpdefault, li.msochpdefault, div.msochpdefault
{margin-right:0cm;margin-left:0cm;font-size:10.0pt;font-family:"Times New Roman", "serif";}
span.emailstyle18
{font-family:"Calibri", "sans-serif";color:#1F497D;}
span.EmailStyle20
{font-family:"Calibri", "sans-serif";color:#1F497D;}
.MsoChpDefault
{font-size:10.0pt;}
_filtered {margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.Section1
{}
-->
</STYLE>
<DIV class=Section1>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Calibri', 'sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt">I keep the nulls also, I just don’t let Powerhouse know they are there.</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Calibri', 'sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt">BTW, I never allow nulls in the primary keys….that makes PH go weird.</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Calibri', 'sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt"> </SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Calibri', 'sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt">I should have included a sample of the layout for you to see what I meant. Sorry</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Calibri', 'sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt"> </SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Courier New'; COLOR: #1f497d; FONT-SIZE: 11pt">Create table Users</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Courier New'; COLOR: #1f497d; FONT-SIZE: 11pt"> (</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Courier New'; COLOR: #1f497d; FONT-SIZE: 11pt"> UserId Char(8) Default ' ' Not Null,</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Courier New'; COLOR: #1f497d; FONT-SIZE: 11pt"> UserName Char(40) Default ' ' Not Null,</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Courier New'; COLOR: #1f497d; FONT-SIZE: 11pt"> SecurityClass Char(8),</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Courier New'; COLOR: #1f497d; FONT-SIZE: 11pt"> LanguageId Char(2),</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Courier New'; COLOR: #1f497d; FONT-SIZE: 11pt"> UserDepartmentId Char(8),</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Courier New'; COLOR: #1f497d; FONT-SIZE: 11pt">…</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Courier New'; COLOR: #1f497d; FONT-SIZE: 11pt"> );</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Calibri', 'sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt"> </SPAN></P>
<DIV>
<DIV style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; BORDER-TOP: #b5c4df 1pt solid; BORDER-RIGHT: medium none; PADDING-TOP: 3pt">
<P class=MsoNormal><B><SPAN style="FONT-FAMILY: 'Tahoma', 'sans-serif'; FONT-SIZE: 10pt">From:</SPAN></B><SPAN style="FONT-FAMILY: 'Tahoma', 'sans-serif'; FONT-SIZE: 10pt"> DAN H LAMBSHEAD [mailto:danhl@rogers.com] <BR><B>Sent:</B> February 12, 2010 2:00 PM<BR><B>To:</B> Ken@Langendock.com; Dan Lambshead; powerh-l@lists.sowder.com<BR><B>Subject:</B> Re: [Bulk] Select on a null, with multiple tables</SPAN></P></DIV></DIV>
<P class=MsoNormal> </P>
<DIV>
<DIV>
<P class=MsoNormal>Hi Ken,</P></DIV>
<DIV>
<P class=MsoNormal> </P></DIV>
<DIV>
<P class=MsoNormal> 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. </P></DIV>
<DIV>
<P class=MsoNormal> </P></DIV>
<DIV>
<P class=MsoNormal> 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.</P></DIV>
<DIV>
<P class=MsoNormal> </P></DIV>
<DIV>
<P class=MsoNormal>Dan </P></DIV>
<DIV>
<P class=MsoNormal> </P>
<DIV>
<DIV style="TEXT-ALIGN: center" class=MsoNormal align=center><SPAN style="FONT-FAMILY: 'Tahoma', 'sans-serif'; FONT-SIZE: 10pt">
<HR align=center SIZE=1 width="100%">
</SPAN></DIV>
<P style="MARGIN-BOTTOM: 12pt" class=MsoNormal><B><SPAN style="FONT-FAMILY: 'Tahoma', 'sans-serif'; FONT-SIZE: 10pt">From:</SPAN></B><SPAN style="FONT-FAMILY: 'Tahoma', 'sans-serif'; FONT-SIZE: 10pt"> Ken Langendock <ken.langendock@rogers.com><BR><B>To:</B> Dan Lambshead <dlambshe@milk.org>; powerh-l@lists.sowder.com<BR><B>Sent:</B> Thu, February 11, 2010 3:18:55 PM<BR><B>Subject:</B> RE: [Bulk] Select on a null, with multiple tables</SPAN></P>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Calibri', 'sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt">Curious, I could never get Nulls to work correctly in Powerhouse.</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Calibri', 'sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt"> </SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Calibri', 'sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt">I set NULL VALUES NOT ALLOWED in the PDL and then in the table. </SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Calibri', 'sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt"> </SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Calibri', 'sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt">I do a before row trigger to store nulls in the database as in:</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Calibri', 'sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt"> </SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Courier New'; COLOR: #1f497d; FONT-SIZE: 11pt">CREATE OR REPLACE TRIGGER br_Users</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Courier New'; COLOR: #1f497d; FONT-SIZE: 11pt"> BEFORE INSERT OR UPDATE ON Users</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Courier New'; COLOR: #1f497d; FONT-SIZE: 11pt"> REFERENCING NEW AS NEW OLD AS OLD</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Courier New'; COLOR: #1f497d; FONT-SIZE: 11pt"> FOR EACH ROW DECLARE</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Courier New'; COLOR: #1f497d; FONT-SIZE: 11pt"> BEGIN</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Courier New'; COLOR: #1f497d; FONT-SIZE: 11pt"> :NEW.LanguageId := NULLIF(:NEW.LanguageId,' ');</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Courier New'; COLOR: #1f497d; FONT-SIZE: 11pt"> :NEW.SecurityClass := NULLIF(:NEW.SecurityClass,' ');</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Courier New'; COLOR: #1f497d; FONT-SIZE: 11pt"> :NEW.UserDepartmentId := NULLIF(:NEW.UserDepartmentId,' ');</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Courier New'; COLOR: #1f497d; FONT-SIZE: 11pt"> END;</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Courier New'; COLOR: #1f497d; FONT-SIZE: 11pt">/</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Calibri', 'sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt"> </SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Calibri', 'sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt">That way the Foreign key constraint would not fire if the value was NULL.</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Calibri', 'sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt">Then I can select on “ “ instead of NULL in the powerhouse source code.</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Calibri', 'sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt"> </SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Calibri', 'sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt">Ken</SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Calibri', 'sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt"> </SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Calibri', 'sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt"> </SPAN></P>
<DIV>
<DIV style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; BORDER-TOP: #b5c4df 1pt solid; BORDER-RIGHT: medium none; PADDING-TOP: 3pt">
<P class=MsoNormal><B><SPAN style="FONT-FAMILY: 'Tahoma', 'sans-serif'; FONT-SIZE: 10pt">From:</SPAN></B><SPAN style="FONT-FAMILY: 'Tahoma', 'sans-serif'; FONT-SIZE: 10pt"> powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com [mailto:powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com] <B>On Behalf Of </B>Dan Lambshead<BR><B>Sent:</B> February 11, 2010 2:43 PM<BR><B>To:</B> powerh-l@lists.sowder.com<BR><B>Subject:</B> [Bulk] Select on a null, with multiple tables</SPAN></P></DIV></DIV>
<P class=MsoNormal> </P>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt">Hello,</SPAN></P></DIV>
<DIV>
<P class=MsoNormal> </P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt"> 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 ?</SPAN></P></DIV>
<DIV>
<P class=MsoNormal> </P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt">ie.</SPAN></P></DIV>
<DIV>
<P class=MsoNormal> </P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt">Access Oracle_table_1 &</SPAN></P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt"> Link to Oracle_table_2 opt</SPAN></P></DIV>
<DIV>
<P class=MsoNormal> </P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt">Select if field of Oracle_table_1 = "Y" and &</SPAN></P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt"> field of Oracle_table_2 IS NULL</SPAN></P></DIV>
<DIV>
<P class=MsoNormal> </P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt">Doesn't work, Will always return 0 records !!</SPAN></P></DIV>
<DIV>
<P class=MsoNormal> </P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt">But the following will work and return some records</SPAN></P></DIV>
<DIV>
<P class=MsoNormal> </P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt">1) Select if field of Oracle_table_2 IS NULL</SPAN></P></DIV>
<DIV>
<P class=MsoNormal> </P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt">2) Select if field of Oracle_table_1 = "Y"</SPAN></P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt"> Select Oracle_table_2 if field of Oracle_table_2 IS NULL</SPAN></P></DIV>
<DIV>
<P class=MsoNormal> </P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt">3) DEFINE D_FIELD = field of Oracle_table_2 &<BR> IF NOT field of Oracle_table_2 IS NULL ELSE 0</SPAN></P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt"> </SPAN></P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt"> Select if field of Oracle_table_1 = "Y" and & </SPAN></P>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt"> D_FIELD = 0</SPAN></P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt"> </SPAN></P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt"> (This will work for me since the field is actually a date type which cannot be 0 in the first place</SPAN></P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt"> </SPAN></P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt"> </SPAN></P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt">Thanks</SPAN></P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt"> </SPAN></P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt">Dan</SPAN></P></DIV></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt"> </SPAN></P></DIV>
<DIV>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt"> </SPAN></P></DIV>
<DIV>
<P class=MsoNormal> </P></DIV>
<DIV>
<P class=MsoNormal> </P></DIV>
<DIV>
<P class=MsoNormal> </P></DIV>
<P><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt">No virus found in this incoming message.<BR>Checked by AVG - www.avg.com<BR>Version: 9.0.733 / Virus Database: 271.1.1/2680 - Release Date: 02/11/10 02:35:00</SPAN></P></DIV></DIV></DIV></DIV>
<P><SPAN style="FONT-FAMILY: 'Arial', 'sans-serif'; FONT-SIZE: 10pt">No virus found in this incoming message.<BR>Checked by AVG - www.avg.com<BR>Version: 9.0.733 / Virus Database: 271.1.1/2682 - Release Date: 02/12/10 02:35:00</SPAN></P></DIV></DIV></DIV></div></body></html>