<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=utf-8">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
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
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
p.msochpdefault, li.msochpdefault, div.msochpdefault
{mso-style-name:msochpdefault;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:10.0pt;
font-family:"Times New Roman","serif";}
span.emailstyle18
{mso-style-name:emailstyle18;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page Section1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>I keep the nulls also, I just don’t let Powerhouse know they are
there.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>BTW, I never allow nulls in the primary keys….that makes PH go weird.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>I should have included a sample of the layout for you to see
what I meant. Sorry<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'>Create table Users<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'> (<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'> UserId Char(8) Default ' ' Not Null,<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'> UserName Char(40) Default ' ' Not Null,<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'> SecurityClass Char(8),<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'> LanguageId Char(2),<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'> UserDepartmentId Char(8),<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'>…<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'> );<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> 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<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<div>
<div>
<p class=MsoNormal>Hi Ken,<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></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. <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></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.<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal>Dan <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
<div>
<div class=MsoNormal align=center style='text-align:center'><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>
<hr size=1 width="100%" align=center>
</span></div>
<p class=MsoNormal style='margin-bottom:12.0pt'><b><span style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'>From:</span></b><span style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'> 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><o:p></o:p></p>
<div>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Curious, I could never get Nulls to work correctly in
Powerhouse.</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> </span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>I set NULL VALUES NOT ALLOWED in the PDL and then in the table. </span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> </span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>I do a before row trigger to store nulls in the database as in:</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> </span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'>CREATE OR REPLACE TRIGGER br_Users</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'> BEFORE INSERT OR UPDATE ON Users</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'> REFERENCING NEW AS NEW OLD AS OLD</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'> FOR EACH ROW DECLARE</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'> BEGIN</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'> :NEW.LanguageId := NULLIF(:NEW.LanguageId,'
');</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'> :NEW.SecurityClass :=
NULLIF(:NEW.SecurityClass,' ');</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'> :NEW.UserDepartmentId :=
NULLIF(:NEW.UserDepartmentId,' ');</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'> END;</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'>/</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> </span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>That way the Foreign key constraint would not fire if the value
was NULL.</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Then I can select on “ “ instead of NULL in the powerhouse
source code.</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> </span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Ken</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> </span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> </span><o:p></o:p></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> 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><o:p></o:p></p>
</div>
</div>
<p class=MsoNormal> <o:p></o:p></p>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Hello,</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>
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><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>ie.</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Access
Oracle_table_1 &</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>
Link to Oracle_table_2 opt</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Select
if field of Oracle_table_1 = "Y" and &</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>
field of Oracle_table_2 IS NULL</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Doesn't
work, Will always return 0 records !!</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>But
the following will work and return some records</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>1)
Select if field of Oracle_table_2 IS NULL</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>2)
Select if field of Oracle_table_1 = "Y"</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>
Select Oracle_table_2 if field of Oracle_table_2 IS NULL</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>3)
DEFINE D_FIELD = field of Oracle_table_2 &<br>
IF NOT field of Oracle_table_2 IS
NULL ELSE 0</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>
</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>
Select if field of Oracle_table_1 = "Y" and & </span><o:p></o:p></p>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>
D_FIELD = 0</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'> </span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>
(This will work for me since the field is actually a date type which cannot be
0 in the first place</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'> </span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'> </span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Thanks</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'> </span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Dan</span><o:p></o:p></p>
</div>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>
</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'> </span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<p><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>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><o:p></o:p></p>
</div>
</div>
</div>
</div>
<p><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>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><o:p></o:p></p>
</div>
</body>
</html>