Looking for SORT Specification Guru

[Robert Atkinson]

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.

[Antoniov.]

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

@Antoniov

[Uwe Zessin]

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]

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]

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 · Accepted solution]

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]

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

Do you see this as a performance problem?

Rob.

[Robert Atkinson]

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]

> 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]

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.

[Robert Atkinson]

Ignore me - the definition for the 'date' field also included the time, so it couldn't sub-sort on the report name!

Understand what you're saying about PERL. I've been trying to learn bits of it, but it's a struggle.

What I really need is a mentor who knows Perl::VMS to stop me tripping up over things. Wanna volunteer?

Rob.

[Robert Atkinson]

BTW - This application is designed to deliver PDF reports to the end recipient via a web interface. I've uploaded a screenshot of what it currently looks like.

The RMS report masterfile is made up of REPORTNAME, PDFFILE, GROUP, DATE, etc.

The reason I'm using SORT/MERGE is because it's the fastest way I've found (so far) to select a bunch of records using simple selection criteria.

I also use a set of modules written in-house called DCF. Any application, i.e. Perl, would need to interface with these modules, as the field definitions are dynamic and held in metafiles, not hardcoded.

Rob.

[Hein van den Heuvel]

Good one on the time coming before the report!

If you are doing a web interface, and are concerned with speed, then you may want to consider the Calleable interface to Sort. Specially it has this 'record mode' where the records do not have to 'touch the ground'. You can just feed records from one side (as you select based on groups) and take the sorted records out on the other side feeding them into the next processing/formatting fase.
- You'd still need a specification file (pass in SOR$SPEC_FILE).

Hein.

[Robert Atkinson]

Ah...that's where the problem starts!

I don't have access to any compiled languages that could use API's or system services.

Almost all of the utilities here are written in DCL, so I've had to come up with clever ways to make it act like a real language or speed things up, hence the use of sort/merge for this particular application.

For the time being, Perl is probably the closest I'm going to get to anything fast.

Rob.

[Martin P.J. Zinser]

Hello Robert,

not having access to any compiled languages is not true ;-) You do have at least the Macro compiler on your system as part of the standard VMS installation. Additionally Bliss is available on the Freeware CD. You can certainly call system services from both of these languages.

If you do have a license also DTR might be a suitabale candidate for the sort of work you try to do (No, I don't mind the Pun ;-)

Greetings, Martin