Operating System - OpenVMS
1748236 Members
3655 Online
108759 Solutions
New Discussion юеВ

Re: Looking for SORT Specification Guru

 
SOLVED
Go to solution
Robert Atkinson
Respected Contributor

Looking for SORT Specification Guru

Hi.

I'm using SORT/MERGE Specification files to produce a report based on specific selection criteria.

I've got the basics of it working, but would like a hand in taking this further - it's really the syntax that I'm struggling with.

The current format is :-

/field=(name=report,pos=1,size=15)
/field=(name=ledger,pos=16,size=2)
/field=(name=group,pos=83,size=10)
/field=(name=date,pos=57,size=16)
/field=(name=record,pos=1,size=200)
/condition=(name=select_report,
test=(report eq "DIRECTORY " and
date ge "2003051000000000" and date le "2004051199999999"))
/include=(condition=select_report,key=report,key=date,data=record)

I have 4 selectable fields, REPORT, LEDGER_CODE, START_DATE and END_DATE that need to be put into the specification, but I want to do this is an organised way.

If you can help me with these, please let me know.

Robert.
14 REPLIES 14
Antoniov.
Honored Contributor

Re: Looking for SORT Specification Guru

Hi Robert,
just for curiosity, what version of sort/merge are you using?
I never seen name token in /key qualifier.

@Antoniov
Antonio Maria Vigliotti
Uwe Zessin
Honored Contributor

Re: Looking for SORT Specification Guru

Hello Antoniov,
Robert is using a 'specification' file for SORT/MERGE. Here is a URL for bed-time reading ;-)

http://h71000.www7.hp.com/DOC/731FINAL/6489/6489pro_022.html#srt_specification_file
.
Robert Atkinson
Respected Contributor

Re: Looking for SORT Specification Guru

Here's a specific question. How do I write the follwing test? :-

date ge "dddd" and date ge "dddd" and report eq "rrrr" and group eq "xxxx" or group eq "yyyy"

Basically, I want to select multiple groups for a given date range for a given report name.

Can I use parenthesis around the tests to make this work?

Rob.
Robert Atkinson
Respected Contributor

Re: Looking for SORT Specification Guru

I can tell you that this DOESN'T work...

/field=(name=report,pos=1,size=15)
/field=(name=ledger,pos=16,size=2)
/field=(name=group,pos=83,size=10)
/field=(name=recipient,pos=73,size=10)
/field=(name=date,pos=57,size=16)
/field=(name=record,pos=1,size=200)
/condition=(name=select_report,test=(date ge "2004051000000000" and date le "2004051299999999" and (group eq "DAVES "))
/include=(condition=select_report,key=report,key=date,data=record)
Hein van den Heuvel
Honored Contributor
Solution

Re: Looking for SORT Specification Guru

I think you need two includes, each triggered by their own condition, one for each group (the OR clause).

Something like this:
[Untested....]

/field=(name=report,pos=1,size=15)
/field=(name=ledger,pos=16,size=2)
/field=(name=group,pos=83,size=10)
/field=(name=date,pos=57,size=16)
/key=date
/condition=(name=group1,
test=(report eq "DIRECTORY " and
date ge "2003051000000000" and date le "2004051199999999" and group eq "xxx"))
/condition=(name=group2,
test=(report eq "DIRECTORY " and
date ge "2003051000000000" and date le "2004051199999999" and group eq "yyy"))
/include=(condition=group1)
/include=(condition=group2)

The "DIRECTORY " would need to be spaced out to 15 characters obviously.

If this suggestion fails to do the job, then please include a 20 - 50 line data example in a txt attachment. It should also have the 2 - 40 lines result expected from the example input data.

Hope this helps some,
Hein.

Robert Atkinson
Respected Contributor

Re: Looking for SORT Specification Guru

Hein - there could be a maximum of 200 groups, so potentially 200 tests.

Do you see this as a performance problem?

Rob.
Robert Atkinson
Respected Contributor

Re: Looking for SORT Specification Guru

Hein - I tried your idea and it works a treat. There is some performance degradation, but I can live with it.

In case you're interested, this is how the specification looks :-

/field=(name=report,pos=1,size=15)
/field=(name=ledger,pos=16,size=2)
/field=(name=group,pos=83,size=10)
/field=(name=recipient,pos=73,size=10)
/field=(name=date,pos=57,size=16)
/field=(name=record,pos=1,size=200)
/condition=(name=select_report1,
test=(date ge "2004051000000000" and date le "2004051399999999"
and report eq "DIRECTORY "

and group eq "ACCOUNTS "
))
/include=(condition=select_report1,key=report,key=date,data=record)
/condition=(name=select_report2,
test=(date ge "2004051000000000" and date le "2004051399999999"
and report eq "DIRECTORY "

and group eq "ITOPS "
))
/include=(condition=select_report2,key=report,key=date,data=record)
/condition=(name=select_report3,
test=(date ge "2004051000000000" and date le "2004051399999999"
and report eq "DIRECTORY "

and group eq "WHMAN "
))
/include=(condition=select_report3,key=report,key=date,data=record)
/condition=(name=select_report4,
test=(date ge "2004051000000000" and date le "2004051399999999"
and report eq "DIRECTORY "

and group eq "ALICE "
))
/include=(condition=select_report4,key=report,key=date,data=record)
/condition=(name=select_report5,
test=(date ge "2004051000000000" and date le "2004051399999999"
and report eq "DIRECTORY "

and group eq "TEST "
))
/include=(condition=select_report5,key=report,key=date,data=record)
/condition=(name=select_report6,
test=(date ge "2004051000000000" and date le "2004051399999999"
and report eq "DIRECTORY "

and group eq "TEST2 "
))
/include=(condition=select_report6,key=report,key=date,data=record)
/condition=(name=select_report7,
test=(date ge "2004051000000000" and date le "2004051399999999"
and report eq "DIRECTORY "

and group eq "TEST3 "
))
/include=(condition=select_report7,key=report,key=date,data=record)
/condition=(name=select_report8,
test=(date ge "2004051000000000" and date le "2004051399999999"
and report eq "DIRECTORY "

and group eq "DAVES "
))
/include=(condition=select_report8,key=report,key=date,data=record)
/condition=(name=select_report9,
test=(date ge "2004051000000000" and date le "2004051399999999"
and report eq "DIRECTORY "

and group eq "TEST4 "
))
/include=(condition=select_report9,key=report,key=date,data=record)
/condition=(name=select_report10,
test=(date ge "2004051000000000" and date le "2004051399999999"
and report eq "DIRECTORY "

and group eq "TEST5 "
))
/include=(condition=select_report10,key=report,key=date,data=record)
/condition=(name=select_report11,
test=(date ge "2004051000000000" and date le "2004051399999999"
and report eq "DIRECTORY "

and group eq "TEST6 "
))
/include=(condition=select_report11,key=report,key=date,data=record)
/condition=(name=select_report12,
test=(date ge "2004051000000000" and date le "2004051399999999"
and report eq "DIRECTORY "

and group eq "TEST7 "
))
/include=(condition=select_report12,key=report,key=date,data=record)
/condition=(name=select_report13,
test=(date ge "2004051000000000" and date le "2004051399999999"
and report eq "DIRECTORY "

and group eq "TEST8 "
))
/include=(condition=select_report13,key=report,key=date,data=record)
/condition=(name=select_report14,
test=(date ge "2004051000000000" and date le "2004051399999999"
and report eq "DIRECTORY "

and group eq "TEST9 "
))
/include=(condition=select_report14,key=report,key=date,data=record)
/condition=(name=select_report15,
test=(date ge "2004051000000000" and date le "2004051399999999"
and report eq "DIRECTORY "

and group eq "TEST10 "
))
/include=(condition=select_report15,key=report,key=date,data=record)
/condition=(name=select_report16,
test=(date ge "2004051000000000" and date le "2004051399999999"
and report eq "DIRECTORY "

and group eq "RHGAD "
))
/include=(condition=select_report16,key=report,key=date,data=record)
Hein van den Heuvel
Honored Contributor

Re: Looking for SORT Specification Guru


> Hein - there could be a maximum of 200 groups, so potentially 200 tests.
> Do you see this as a performance problem?

I don't do you? :-).
But then I don't see anything.
That is.. at some point it becomes important to know whether this is a hourly/monthly/yearly job.
Dealing with 1 million or 100 million records?
Taking 'roughly' 1 minute, 10 minutes or an hour?
How selective is the date part? 10% of the original file? 90%?
How selective is the group? 10% of the file? 90%?


- Since the date criteria appears the same for all groups, I would to make two runs.
First filter for the right dates & report, then prune by group and get proper sort order
Or first select those with valid groups and then do the date and sort.

- Check out alternative pre-selection tools, befoire the sort. Maybe an AWK or PERL script is really more convenient. In perl the valid groups could be keys in an assiciative array and then you just test with: next unless $valid{$group};


- Check out collating sequences or /KEY modifier to deal with groups. Could you 'translate' valid groups into a key value 'valid' and then prune for that?
Using that you can make "JAN" sort before "FEB" followed by "MAR", "APR" :-).


Glad to hear it worked though!
Hein.

Robert Atkinson
Respected Contributor

Re: Looking for SORT Specification Guru

I do seem to have a sort order problem.

As far as I know, "key=date,key=report" should sort the data by date then by report name, but the records are not being returned in the correct order :-

/field=(name=report,pos=1,size=15)
/field=(name=ledger,pos=16,size=2)
/field=(name=group,pos=83,size=10)
/field=(name=recipient,pos=73,size=10)
/field=(name=date,pos=57,size=16)
/field=(name=record,pos=1,size=200)
/condition=(name=select_report1,
test=(date ge "2004051000000000" and date le "2004051399999999"


and group eq "RHGAD "
))
/include=(condition=select_report1,key=date,key=report,data=record)
/condition=(name=select_report_for_user,
test=(date ge "2004051000000000" and date le "2004051399999999"


and group eq "RAA "
))
/include=(condition=select_report_for_user,key=date,key=report,data=record)

Should I be using "/KEY=" instead? When I tried this originally, SORT complained that there was no key field in the condition statement.