Hi Dan:
If you sort descending then the NULL values may come up first. At least that's how Oracle doe s it.
Your data will of course go from highest to lowest in the collating sequence.
So you could make for yourself a special purpose dictionary where the collating sequence is the
reverse of your regular collating sequence and then sort descending. i.e. replace the character set in the PDL code.
This may work for character columns only.
Some databases support ORDER BY .... NULLS FIRST
IF your database does support the feature then you may be able create a cursor with that syntax in it.
It would be nice if PowerHouse did as well.
ie SORT ON <item1> NULLS FIRST , ON <item2> DESCENDING NULLS LAST etc
You could create a suite of views with a calculated field for each nullable column.
You probably could create a Quiz or QTP process to create the metadata for these views.
Question:- Do you want a control break to occur between a null value column and a blank column?
Regards,
Peter
sSubject: RE: Sorting of null values in Powerhouse
Date: Wed, 25 Feb 2009 09:50:58 +0000
From: gavin.lloyd at fmglobal.com
To: dlambshe at milk.org; powerh-l at lists.sowder.com
Dan,
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!
Regards,
Gavin.
From: Ken Langendock [mailto:ken.langendock at rogers.com]
Sent: 24 February 2009 15:29
To: 'Dan Lambshead'; powerh-l at lists.sowder.com
Subject: RE: Sorting of null values in Powerhouse
I don’t think you have much choice.
You either use a DEFINE before the SORT, which doesn’t impact performance very much.
Or
If you have a QTP extract, you could use an ITEM fieldname FINAL “ “ if NULL EQ fieldname OF subfile
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 24, 2009 9:17 AM
To: powerh-l at lists.sowder.com
Subject: Sorting of null values in Powerhouse
Hello,
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.
Of course we could do a define, setting nulls to spaces and sort on that, but still wondering if there is an easier way.
Thanks
Dan
No virus found in this incoming message.
Checked by AVG.
Version: 7.5.552 / Virus Database: 270.11.3/1968 - Release Date: 23/02/2009 6:22 PM
No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.552 / Virus Database: 270.11.3/1968 - Release Date: 23/02/2009 6:22 PM
Registered No. 755780 England
Registered Office: FM Insurance Company Limited
1 Windsor Dials, Windsor,
Berkshire, UK, SL4 1RS
Regulated by the Financial Services Authority.
VAT No. G.B.: 792 4276 02
_________________________________________________________________
So many new options, so little time. Windows Live Messenger.
http://www.microsoft.com/windows/windowslive/products/messenger.aspx
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20090225/5e3af9a0/attachment.htm