<html>
<head>
<style>
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 10pt;
font-family:Verdana
}
</style>
</head>
<body class='hmmessage'>
Hi Dan:<BR>
<BR>
If you sort descending then the NULL values may come up first. At least that's how Oracle doe s it. <BR>
Your data will of course go from highest to lowest in the collating sequence.<BR>
<BR>
So you could make for yourself a special purpose dictionary where the collating sequence is the <BR>
reverse of your regular collating sequence and then sort descending. i.e. replace the character set in the PDL code.<BR>
This may work for character columns only.<BR>
<BR>
Some databases support ORDER BY .... NULLS FIRST <BR>
<BR>
IF your database does support the feature then you may be able create a cursor with that syntax in it.<BR>
It would be nice if PowerHouse did as well.<BR>
ie SORT ON <item1> NULLS FIRST , ON <item2> DESCENDING NULLS LAST etc<BR>
<BR>
You could create a suite of views with a calculated field for each nullable column.<BR>
You probably could create a Quiz or QTP process to create the metadata for these views.<BR>
<BR>
Question:- Do you want a control break to occur between a null value column and a blank column?<BR>
<BR>
Regards,<BR>
Peter<BR>
<BR>
<BR>
<BR>
<BR> <BR>
<HR id=stopSpelling>
sSubject: RE: Sorting of null values in Powerhouse<BR>Date: Wed, 25 Feb 2009 09:50:58 +0000<BR>From: gavin.lloyd@fmglobal.com<BR>To: dlambshe@milk.org; powerh-l@lists.sowder.com<BR><BR>
<STYLE>
.ExternalClass p.EC_MsoNormal, .ExternalClass li.EC_MsoNormal, .ExternalClass div.EC_MsoNormal
{margin-bottom:.0001pt;font-size:12.0pt;font-family:'Times New Roman','serif';}
.ExternalClass a:link, .ExternalClass span.EC_MsoHyperlink
{color:blue;text-decoration:underline;}
.ExternalClass a:visited, .ExternalClass span.EC_MsoHyperlinkFollowed
{color:purple;text-decoration:underline;}
.ExternalClass p
{margin-right:0cm;margin-left:0cm;font-size:12.0pt;font-family:'Times New Roman','serif';}
.ExternalClass span.EC_EmailStyle18
{font-family:'Calibri','sans-serif';color:#1F497D;}
.ExternalClass span.EC_EmailStyle19
{font-family:'Tahoma','sans-serif';color:blue;font-weight:normal;font-style:normal;text-decoration:none none;}
.ExternalClass .EC_MsoChpDefault
{font-size:10.0pt;}
@page Section1
{size:612.0pt 792.0pt;}
.ExternalClass div.EC_Section1
{page:Section1;}
</STYLE>
<DIV>
<DIV class=EC_Section1>
<P class=EC_MsoNormal><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Tahoma','sans-serif'">Dan,</SPAN></P>
<P class=EC_MsoNormal><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Tahoma','sans-serif'"> </SPAN></P>
<P class=EC_MsoNormal><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Tahoma','sans-serif'">1 option, which you may consider a little outlandish, would be to not create null values in the field in the first place. You can set an initial value of space at the database level. You would of course loose the ability to query on nulls using SQL, etc but it would resolve your sort issue. Your decision as to whether the change is worth the effort!</SPAN></P>
<P class=EC_MsoNormal><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Tahoma','sans-serif'"> </SPAN></P>
<P class=EC_MsoNormal><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Tahoma','sans-serif'">Regards,</SPAN><SPAN style="COLOR: blue"><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Tahoma','sans-serif'">Gavin.</SPAN><SPAN style="COLOR: blue"></SPAN></P>
<P class=EC_MsoNormal><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Tahoma','sans-serif'"> </SPAN></P>
<DIV>
<DIV style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0cm; BORDER-TOP: #b5c4df 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: medium none; PADDING-TOP: 3pt; BORDER-BOTTOM: medium none">
<P class=EC_MsoNormal><B><SPAN lang=EN-US style="FONT-SIZE: 10pt; FONT-FAMILY: 'Tahoma','sans-serif'">From:</SPAN></B><SPAN lang=EN-US style="FONT-SIZE: 10pt; FONT-FAMILY: 'Tahoma','sans-serif'"> Ken Langendock [mailto:ken.langendock@rogers.com] <BR><B>Sent:</B> 24 February 2009 15:29<BR><B>To:</B> 'Dan Lambshead'; powerh-l@lists.sowder.com<BR><B>Subject:</B> RE: Sorting of null values in Powerhouse</SPAN></P></DIV></DIV>
<P class=EC_MsoNormal> </P>
<P class=EC_MsoNormal><SPAN lang=EN-US style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'">I don’t think you have much choice.</SPAN></P>
<P class=EC_MsoNormal><SPAN lang=EN-US style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'"> </SPAN></P>
<P class=EC_MsoNormal><SPAN lang=EN-US style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'">You either use a DEFINE before the SORT, which doesn’t impact performance very much.</SPAN></P>
<P class=EC_MsoNormal><SPAN lang=EN-US style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'">Or </SPAN></P>
<P class=EC_MsoNormal><SPAN lang=EN-US style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'">If you have a QTP extract, you could use an ITEM fieldname FINAL “ “ if NULL EQ fieldname OF subfile</SPAN></P>
<P class=EC_MsoNormal><SPAN lang=EN-US style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'"> </SPAN></P>
<P class=EC_MsoNormal><SPAN lang=EN-US style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'">Ken</SPAN></P>
<P class=EC_MsoNormal><SPAN lang=EN-US style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'"> </SPAN></P>
<DIV>
<DIV style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0cm; BORDER-TOP: #b5c4df 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: medium none; PADDING-TOP: 3pt; BORDER-BOTTOM: medium none">
<P class=EC_MsoNormal><B><SPAN lang=EN-US style="FONT-SIZE: 10pt; FONT-FAMILY: 'Tahoma','sans-serif'">From:</SPAN></B><SPAN lang=EN-US style="FONT-SIZE: 10pt; 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 24, 2009 9:17 AM<BR><B>To:</B> powerh-l@lists.sowder.com<BR><B>Subject:</B> Sorting of null values in Powerhouse</SPAN></P></DIV></DIV>
<P class=EC_MsoNormal><SPAN lang=EN-US> </SPAN></P>
<DIV>
<P class=EC_MsoNormal><SPAN lang=EN-US style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'">Hello,</SPAN><SPAN lang=EN-US></SPAN></P></DIV>
<DIV>
<P class=EC_MsoNormal><SPAN lang=EN-US> </SPAN></P></DIV>
<DIV>
<P class=EC_MsoNormal><SPAN lang=EN-US style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'"> Does anyone know if there is an option to sort null values so that they appear at the top (in Oracle, as they would if they were spaces, as previously we had in indexed files) rather than the bottom. This is for OpenVMS and Powerhouse version 8.40.D1.</SPAN><SPAN lang=EN-US></SPAN></P></DIV>
<DIV>
<P class=EC_MsoNormal><SPAN lang=EN-US style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'"> Of course we could do a define, setting nulls to spaces and sort on that, but still wondering if there is an easier way.</SPAN><SPAN lang=EN-US></SPAN></P></DIV>
<DIV>
<P class=EC_MsoNormal><SPAN lang=EN-US> </SPAN></P></DIV>
<DIV>
<P class=EC_MsoNormal><SPAN lang=EN-US style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'">Thanks</SPAN><SPAN lang=EN-US></SPAN></P></DIV>
<DIV>
<P class=EC_MsoNormal><SPAN lang=EN-US> </SPAN></P></DIV>
<DIV>
<P class=EC_MsoNormal><SPAN lang=EN-US style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'">Dan</SPAN><SPAN lang=EN-US></SPAN></P></DIV>
<P class=EC_MsoNormal><SPAN lang=EN-US> </SPAN></P>
<SPAN lang=EN-US style="FONT-SIZE: 10pt">No virus found in this incoming message.<BR>Checked by AVG.<BR>Version: 7.5.552 / Virus Database: 270.11.3/1968 - Release Date: 23/02/2009 6:22 PM</SPAN><SPAN lang=EN-US></SPAN><BR>
<P class=EC_MsoNormal> </P>
<SPAN style="FONT-SIZE: 10pt">No virus found in this outgoing message.<BR>Checked by AVG.<BR>Version: 7.5.552 / Virus Database: 270.11.3/1968 - Release Date: 23/02/2009 6:22 PM</SPAN><BR></DIV></DIV>
<DIV> </DIV>
<DIV>
<P class=EC_MsoNormal><SPAN lang=EN-GB style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial"><FONT color=gray>Registered No. 755780 England</FONT></SPAN></P>
<P class=EC_MsoNormal><SPAN lang=EN-GB style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial"><FONT color=gray>Registered Office: FM Insurance Company Limited</FONT></SPAN></P>
<P class=EC_MsoNormal><SPAN lang=EN-GB style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial"><FONT color=gray>1 Windsor Dials, Windsor,</FONT></SPAN></P>
<P class=EC_MsoNormal><SPAN lang=EN-GB style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial"></SPAN><SPAN lang=EN-GB style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial"><FONT color=gray>Berkshire, UK, SL4 1RS</FONT></SPAN></P>
<P class=EC_MsoNormal><SPAN lang=EN-GB style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial"><FONT color=gray>Regulated by the Financial Services Authority.</FONT></SPAN></P>
<P class=EC_MsoNormal><SPAN lang=EN-GB style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial"><FONT color=gray>VAT No. G.B.: 792 4276 02</FONT></SPAN></P></DIV><br /><hr />So many new options, so little time. <a href='http://www.microsoft.com/windows/windowslive/products/messenger.aspx' target='_new'>Windows Live Messenger.</a></body>
</html>