Query on IFTHEN=(WHEN



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Query on IFTHEN=(WHEN

Postby Aki88 » Wed Jan 20, 2016 12:45 pm

Hello,

My apologies for this redundant done-to-death query, on what is turning out be 'dumb guy's day- Wednesday'. :oops:

Sample data (I've repeated the records below, in actual scenario they can be same/different between each 'XXXX' and '999' group; all records are of fixed length) -

SORTIN:

XXXXDD/MM/YYYYNNNNNNNNNMMMMMMMMMMMMMMM     
ABCDEFGDSLFHSO                   LFHAODSDFKG
ABAFKSAPDJFLOS                   JEFAJHSDFKG
ASFJSFGDSLFHSO                   LFHAODSDFKG
ABCDSFALJHSDSO                   LFHAODSDFKG
999EEEEEEEEEFFFFFFFFFFFFFFF                 
XXXXDD/MM/YYYYNNNNNNNNNMMMMMMMMMMMMMMM     
ABCDEFGDSLFHSO                   LFHAODSDFKG
ABAFKSAPDJFLOS                   JEFAJHSDFKG
ASFJSFGDSLFHSO                   LFHAODSDFKG
ABCDSFALJHSDSO                   LFHAODSDFKG
999EEEEEEEEEFFFFFFFFFFFFFFF                 
XXXXDD/MM/YYYYNNNNNNNNNMMMMMMMMMMMMMMM     
ABCDEFGDSLFHSO                   LFHAODSDFKG
ABAFKSAPDJFLOS                   JEFAJHSDFKG
ASFJSFGDSLFHSO                   LFHAODSDFKG
ABCDSFALJHSDSO                   LFHAODSDFKG
999EEEEEEEEEFFFFFFFFFFFFFFF                 
XXXXDD/MM/YYYYNNNNNNNNNMMMMMMMMMMMMMMM     
ABCDEFGDSLFHSO                   LFHAODSDFKG
ABAFKSAPDJFLOS                   JEFAJHSDFKG
ASFJSFGDSLFHSO                   LFHAODSDFKG
ABCDSFALJHSDSO                   LFHAODSDFKG
999EEEEEEEEEFFFFFFFFFFFFFFF                 
XXXXDD/MM/YYYYNNNNNNNNNMMMMMMMMMMMMMMM     
ABCDEFGDSLFHSO                   LFHAODSDFKG
ABAFKSAPDJFLOS                   JEFAJHSDFKG
ASFJSFGDSLFHSO                   LFHAODSDFKG
ABCDSFALJHSDSO                   LFHAODSDFKG
999EEEEEEEEEFFFFFFFFFFFFFFF                 



I need to generate a consolidated file for which output has below:
1. The first record in the output file should be as below; the only difference here being - the 'N' and 'M' in this record have to be a sum of all the 'N', 'M' that have appeared against the 'XXXX' or the header records:

XXXXDD/MM/YYYYNNNNNNNNNMMMMMMMMMMMMMMM   


2. The second record onwards, are the detail records; which is a mere SORT COPY of the records other than 'XXXX' and '999'
3. The last record in the file will be '999' record; here again, 'E' and 'F' that appear, have to be sum of all 'E' and 'F' that have appeared against the '999' or tail records.

999EEEEEEEEEFFFFFFFFFFFFFFF


I was able to complete this in two passes; by seperating the 'X' and '9' records in a different file and then merging them with the original data; I am trying to do the same in one go itself.
One way was to build all records in INREC processing, and then subsequently write them to output.
But the point where I am stuck is, how do I build the consolidated header record (I've been trying to GROUP the data, but am totally off the rail on building header).

My apologies for this simple query, but I'm somehow/somewhere missing the FINE PRINT. Any pointers will be really helpful.

Thank you.
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times

Re: Query on IFTHEN=(WHEN

Postby BillyBoyo » Wed Jan 20, 2016 2:03 pm

Can you show the two steps which work, please?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Query on IFTHEN=(WHEN

Postby Aki88 » Wed Jan 20, 2016 2:31 pm

Hello Billy,

For the two step solution:
a. Break the data into three files- head/detail/tail, building the summation in this step itself.
b. DD concatenate the three files created in (a) as: head/detail/tail; and execute SORT COPY, to obtain the final dataset.

 SORT FIELDS=COPY                                       
 OUTFIL FILES=01,NODETAIL,REMOVECC,                     
        INCLUDE=(1,3,CH,EQ,C'XXXX'),                     
        TRAILER1=(1:1,14,15:TOT=(15,9,ZD,TO=ZD,LENGTH=9),
                  24:TOT=(24,15,ZD,TO=ZD,LENGTH=15))     
 OUTFIL FILES=02,                                       
        OMIT=((1,3,CH,EQ,C'XXXX'),OR,(1,3,CH,EQ,C'999'))
 OUTFIL FILES=03,NODETAIL,REMOVECC,                     
        INCLUDE=(1,3,CH,EQ,C'999'),                     
        TRAILER1=(1:'999',4:TOT=(4,9,ZD,TO=ZD,LENGTH=9),
                  13:TOT=(13,15,ZD,TO=ZD,LENGTH=15))     


Output of the above SORT card:

SORTOF01:
XXXXDD/MM/YYYY000000032000000000000053

SORTOF02:
ABCDEFGDSLFHSO                   LFHAODSDFKG
ABAFKSAPDJFLOS                   JEFAJHSDFKG
ASFJSFGDSLFHSO                   LFHAODSDFKG
ABCDSFALJHSDSO                   LFHAODSDFKG
ABCDEFGDSLFHSO                   LFHAODSDFKG
ABAFKSAPDJFLOS                   JEFAJHSDFKG
ASFJSFGDSLFHSO                   LFHAODSDFKG
ABCDSFALJHSDSO                   LFHAODSDFKG
ABCDEFGDSLFHSO                   LFHAODSDFKG
ABAFKSAPDJFLOS                   JEFAJHSDFKG
ASFJSFGDSLFHSO                   LFHAODSDFKG
ABCDSFALJHSDSO                   LFHAODSDFKG
ABCDEFGDSLFHSO                   LFHAODSDFKG
ABAFKSAPDJFLOS                   JEFAJHSDFKG
ASFJSFGDSLFHSO                   LFHAODSDFKG
ABCDSFALJHSDSO                   LFHAODSDFKG
ABCDEFGDSLFHSO                   LFHAODSDFKG

SORTOF03:
999000000035000000000000025


Thank you.
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times

Re: Query on IFTHEN=(WHEN

Postby BillyBoyo » Wed Jan 20, 2016 5:23 pm

Due to the need to update the first record with details from later records, this is not a bad way to do it.

For your data file, you could use SAVE instead of the INCLUDE= with reversed conditions.

You could use JOINKEYS with same DSN on both inputs. In the JNF1CNTL, OMIT the headers and trailers. On the JNF2CNTL, INCLUDE the headers and trailers, use SUM and you can end up with two records. However, you don't have an indicator on your data-records, so you have to make one temporarily. Since that is also two passes of the data, resource-use is likely to be close and down to your specific data, and your existing one may be the best.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Query on IFTHEN=(WHEN

Postby Aki88 » Wed Jan 20, 2016 5:32 pm

Hello Billy,

BillyBoyo wrote:Due to the need to update the first record with details from later records, this is not a bad way to do it.
---
---
Since that is also two passes of the data, resource-use is likely to be close and down to your specific data, and your existing one may be the best.


You hit the problem right on the head; two passes of data is what I am trying to avoid; the orginal data comes from 'n' input files (n > 10, < 50), and number of records on various days run into 10s of millions. I am trying to avoid this additional overhead, just for the sake of summing up a few records; though it sounds trivial, but that is the only thing being achieved at the end of it all by going through the entire data twice. :(

Thank you.
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times

Re: Query on IFTHEN=(WHEN

Postby BillyBoyo » Wed Jan 20, 2016 6:06 pm

The only way to avoid that, then, is to look for something which reads the same data, but earlier in the jobstream, and then extract the headers/trailers (trailers as well since the data seems unidentifiable as such) there, for later consolidation, in time for your step. Basically, you make that first extra pass disappear by utilising another existing pass of the data.

Alternatively, a design-change, so that data on a "header" does not rely on data which appears after the "header". That's the reason we avoid doing that, and why we have trailers :-)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Query on IFTHEN=(WHEN

Postby Aki88 » Wed Jan 20, 2016 6:51 pm

Hello Billy,

:) Design change will be a behemoth to tame, as all of these files are direct outputs of COBOL programs running across the batch.
Thank you for the pointers; let me see if I can find some loop-hole somewhere which can help me work this out.

Thank you.
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times

Re: Query on IFTHEN=(WHEN

Postby NicC » Wed Jan 20, 2016 7:08 pm

What is the point of having headers/trailers on individual files if they are not being checked? If an error is found on the amalgamted file how are you to decide whic input file is incorrect? Worse, what if there are 2 or more errors that cancel out so that an error is not detected? You should probably have a little program that rads in all the files individually, checking the above and writing out the amalgamated file.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Query on IFTHEN=(WHEN

Postby Aki88 » Wed Jan 20, 2016 8:17 pm

Hello NicC,

A valid point, but the values in question in head/tail records are checksum numbers, generated by the COBOl programs on 'balance' fields.

They are by principle summation of monetary fields, which is cross validated by client when they check the final amalgamated file.
The data in the final amalgamated file can not be incorrect because of the COBOL checks it has to go through during its inception cycle.
Under the scenario there indeed is an error, then the final file itself has to be corrected - bad design indeed, but like I said, modifying the process flow is a giant in its own right.

COBOL program merging was the last resort I was looking at; indeed a complete file build, and at the end of processing reopening the file and updating the header should do the trick.

Thank you.
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post