Records into a query



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

Records into a query

Postby avinashmusic » Fri Mar 21, 2014 12:47 am

Hi Experts,

I have the following requirement

Input file: LRECL:10
DATA0001
DATA00002
DATA03
DATA4
DATA 005
 DATA06


The space you see in the records are deliberate and not typo. The field in the input file might vary in length, i need all the fields within single quotes followed by a comma except for the last record(last record will have single quotes but should exclude comma and include a closed bracket ')')

Expected Output: LRECL:13
'DATA0001',
'DATA00002',
'DATA03',
'DATA4',
'DATA 005',
' DATA06')


Sort card i used is as follows:
SORT FIELDS=COPY
INREC IFTHEN=(WHEN=INIT,
BUILD=(1:1,10,JFY=(SHIFT=LEFT,LEAD=C'''',TRAIL=C''',',LENGTH=13)))
OUTFIL FNAMES=SORTOUT,REMOVECC
TRAILER1=(1,12,C')')


The output i am getting:
'DATA0001',
'DATA00002',
'DATA03',
'DATA4',
'DATA 005',
'DATA06',
'DATA06',   )


Issues with the above output:
1. When there is a space in the first position of the field(last record) it is getting shifted left as i have left justified, but i want the first character to be space(as input) in the output.
2. I was not able to remove the comma in the last record.
3. I am getting a repetition of the last record, as i have used TRAILER1 function. If possible, i want to avoid this, though it is harmless.

Can someone please help me with this?
avinashmusic
 
Posts: 21
Joined: Tue Dec 24, 2013 4:33 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Records into a query

Postby BillyBoyo » Fri Mar 21, 2014 5:13 am

Have a look for the "Smart DFSORT Tricks" publication available from the IBM DFSORT wesite and look at "Add comma at end of all records except the last" on p35.

DATASORT will not "see" the headers/trailers excluded in the INREC, but they will all be there in OUTFIL processing. You can then put the ")" on the record which does not contain a "," (use field-type of SS to do a sub-string search for the ","). You won't then need the TRAILER1.

The SORT is done on 1,1 (a quote) and OPTION EQUALS is the default in DATASORT, so the input order will not change. Since they are only control cards, so you can't have that many, you won't notice the "overhead" of the SORT in any meaningful way.

If you only have one leading space, you can insert a "'" in position 1, copy 1,1 in the BUILD and then do the JFY from 2,9 without the LEAD=.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Records into a query

Postby avinashmusic » Fri Mar 21, 2014 3:58 pm

Hi Billy. . Thanks for your response. But I am not able to collate all what you said. Cos there were too many things. If you could put it up in a sort card, it would be really great. :)
avinashmusic
 
Posts: 21
Joined: Tue Dec 24, 2013 4:33 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Records into a query

Postby BillyBoyo » Fri Mar 21, 2014 6:12 pm

Did you find the document and read the example? Also read the DFSORT Application Programming Guide for ICETOOL generally and DATASORT specifically.

You take the Smart Trick, which is close to what you want, and your code, which is close to what you want, and apply them to get what you want.

Look at it this way: your code has TRAILER1 which causes the duplication, so remove that; the DATASORT puts a comma on each line but the last, which you want, but you want the ")" as well.

Work on that first.

Then your some-time leading space is getting lost in the JFY. So just don't include it (or any other first character) in the JFY, BUILD=(C''',1,1,2,9,JFY...)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Records into a query

Postby avinashmusic » Mon Mar 24, 2014 10:08 pm

Hi Billy.. The smart df sort trick is written for fixed length field. But my field is of variable length.
avinashmusic
 
Posts: 21
Joined: Tue Dec 24, 2013 4:33 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Records into a query

Postby skolusu » Mon Mar 24, 2014 11:29 pm

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//INA      DD DISP=SHR,DSN=YOUR INPUT FB 10 BYTE FILE
//INB      DD DISP=SHR,DSN=SAME INPUT FB 10 BYTE FILE
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  OPTION COPY                                                       
  JOINKEYS F1=INA,FIELDS=(11,8,A),SORTED,NOSEQCK                     
  JOINKEYS F2=INB,FIELDS=(01,8,A),SORTED,NOSEQCK                     
  JOIN UNPAIRED,F1                                                     
  REFORMAT FIELDS=(F1:1,10,?)                                       
                 
  INREC IFOUTLEN=13,IFTHEN=(WHEN=INIT,BUILD=(C'''',1,10,14:11,1)),   
  IFTHEN=(WHEN=INIT,OVERLAY=(1,13,JFY=(SHIFT=RIGHT,TRAIL=X'7D6B'))),
  IFTHEN=(WHEN=(14,1,CH,EQ,C'1'),                                   
  OVERLAY=(13:X,1:1,13,JFY=(SHIFT=LEFT))),                           
  IFTHEN=(WHEN=NONE,BUILD=(1,13,JFY=(SHIFT=LEFT)))                   
//*
//JNF1CNTL DD *                                                     
  INREC OVERLAY=(11:SEQNUM,8,ZD)                                     
//*                                                                 
//JNF2CNTL DD *                                                     
  INREC BUILD=(SEQNUM,8,ZD,START=0)                                 
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Records into a query

Postby avinashmusic » Wed Mar 26, 2014 12:02 am

Thanks kolusu. It works, can you please explain why we are doing that join. I could not understand that part.
avinashmusic
 
Posts: 21
Joined: Tue Dec 24, 2013 4:33 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Records into a query

Postby skolusu » Wed Mar 26, 2014 12:46 am

avinashmusic wrote:Thanks kolusu. It works, can you please explain why we are doing that join. I could not understand that part.


Your goal is to identify the last record and remove the comma. So what do you do? Compare the same file to itself but for file1 you assign the sequence number for every record starting with 1 and for file2 you assign the sequence number for every record starting with 0. what happens when you compare these 2 sequence numbers? The last record will NOT have a match and will be unique.
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Records into a query

Postby avinashmusic » Wed Mar 26, 2014 4:45 pm

Thanks. That was great logic but there will be Another record having the Sequence number as 0 in the second file which will not have a match right? How that is handled?
avinashmusic
 
Posts: 21
Joined: Tue Dec 24, 2013 4:33 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Records into a query

Postby BillyBoyo » Wed Mar 26, 2014 5:32 pm

File with three records
      SeqFrom1 SeqFrom0
DATA1        1        0
DATA2        2        1
DATA3        3        2


The JOIN specifies UNPAIRED,F1 - which gets all matches plus mis-matches for F1. Mis-matches for F2 are simply ignored, not presented to the Main Task.

The Main Task sees

      MatchMarker
DATA1           B
DATA2           B
DATA3           1


Due to the REFORMAT statement, the Main Task does not see the sequence numbers for the above, but they were 1, 2 and 3.

With UNPAIRED,F1,F2 the mis-matched F2 would be included, the REFORMAT would give blanks (or FILL character if specified) and the Match Marker would be 2. There would be four records processed by the Main Task, and sequences would be 0, 1, 2 and 3 (the 0 for F2 only, the 3 for F1 only, and the 1 and 2 for both sources).

You can verify all this through experimentation:

  JOIN UNPAIRED,F1,F2                                                     
  REFORMAT FIELDS=(F1:1,10,?,F1:11,8,F2:1,8)


Remove all the code in the INREC and look at the output you receive.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post