FMTDATA sort specs relationship between record lines

[Raywes88]

Apologies if this is not the ideal section of the forum for this question.

I am trying to replicate the behavior of an older program we have that uses the FMTDATA command. I thought I had a pretty good grasp on how sort specs work; but I do have a question regarding the relationship between the conditions defined on several lines with a blank in position 7.

Take, for example:

HSORTA    19A        3X  04 N
O C  62  62EQCX              
O C  88  88NECX              
O C  89  89EQCC              
O C  93  93NEC
*               
O C 101 101EQCP              
OAC 107 108EQC00   
*          
O C 101 101EQCP              
OAC 107 108EQC         
*      
O C  15  18EQC9000           
I                            
FNC  21  35                  
FNC  55  58 

I read in the "Sort User's Guide and Reference pg.31" that a blank in position 7 indicates "The first of the set of record specifications that is to define this type of record" this seems a little ambigous to me. Are the first 4 lines above in an AND relationship? Omit records with this AND this AND this are true? How do the following two sets of lines figure in that have continuation lines as indicated by the A in position 7?

[TedHolt]

Re: FMTDATA sort specs relationship between record lines

The first four lines would be in an OR relationship, sorta kinda. That is, any record that fits any of these four criteria will be omitted.

In this example, there are seven OMIT criteria. Any record that matches any of the seven will be omitted. The I spec has no selection criterion, so it means "include anything that did not get omitted by the seven omit tests."

The last two lines tell how to sort the file.

[Raywes88]

Re: FMTDATA sort specs relationship between record lines

Thanks! That is what I wanted to know.

EDIT

Maybe while this is fresh in my mind I should write a utility to convert sort specs into a SQL select statement. haha.

[TedHolt]

Re: FMTDATA sort specs relationship between record lines

Maybe while this is fresh in my mind I should write a utility to convert sort specs into a SQL select statement. haha.

That would be some utility. The "mindset" behind the sort utility is quite different from the way SQL works.

[Raywes88]

Re: FMTDATA sort specs relationship between record lines

More of a pipe dream I know; I'm having a hard enough time accomplishing this one by hand.

[Raywes88]

Re: FMTDATA sort specs relationship between record lines

Just for fun.

The SQL statement I wrote will be used to pull data into Excel by our production department. It ended up using 4 sub-selects, 2 inner joins, and 2 left joins. When I formatted it for readability, it came in at 62 lines

- EDIT -
I am sure there are optimizations that can be made. It's not a high priority since this will be run ~2 times a day and it's already almost instantaneous.

[TedHolt]

Re: FMTDATA sort specs relationship between record lines

I am sure there are optimizations that can be made. It's not a high priority since this will be run ~2 times a day and it's already almost instantaneous.

I don't waste my time optimizing something that's performing acceptably.

I'm glad you got your problem solved, Ray.

[Raywes88]

Re: FMTDATA sort specs relationship between record lines

Thanks again for clarifying how the record entries are related in a sort specification. It turned out that I was (more or less) interpreting them correctly and so helped me to narrow down where the bogus values that were being pulled into my result set were coming from.

It got really involved when I realized that on top of the sort specs there were some intermediary RPG programs that were also omitting records from the final version of the extract used to create the original report. The best part is that the end result is a two column table that I have to pull data from 5 different files to produce.