IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Sort Card : To get the records with overlapping dates


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
pecsakthivel123

New User


Joined: 10 Dec 2015
Posts: 2
Location: India

PostPosted: Thu Dec 10, 2015 11:54 pm
Reply with quote

Hi,

I have a requirement to get the records from an input file that has overlapping dates only
based on the key.(Src-ID)

Here are the details of my input file :


RECFM=FB,LRECL=172


Start Position,Length,Type of fields :

SRC-ID (Primary Key) - (1,75,CH)
SUBSCRIBER ID - (76,69,CH)
COV EFF DATE - (145,10,CH)
COV CANCEL DATE - (155-10,CH)
REPORTING CODE - (165,8,CH)


Input File Data :

SRC-ID SUB-ID COV-EFF-DT COV-CAN-DT REP-CODE

1----------75 76---------144 145----154 155-----164 165---172

001000399 6064139420 2015-01-01 2015-12-31 00040004
001000399 6064139420 2016-01-01 9999-12-31 00090009


00100137 1094684540 2015-01-01 2015-12-31 00640064
00100137 1094684540 2015-03-03 9999-12-31 00640064



First two Records having same SRC-ID (Primary Key) does not have overlapping coverage dates and should not come out
Next 2 records having same SRC-ID (Primary Key) have overlapping coverage dates and should come out




Expected Output :


SRC-ID SUB-ID COV-EFF-DT COV-CAN-DT REP-CODE

1-----------75 76------144 145----154 155-----164 165---172

0000100137 1094684540 2015-01-01 2015-12-31 00640064
0000100137 1094684540 2015-03-03 9999-12-31 00640064



I have tried using the sort cards, but am not getting the reqd o/p.

//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN= input Fb 172 byte file,DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN DD *
INREC OVERLAY=(173:1,75,76,20,145,4,150,2,153,2,155,4,160,2,163,2)
SORT FIELDS=(173,111,BI,A),EQUALS
OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(284:SEQNUM,8,ZD,RESTART=(173,9))),
IFTHEN=(WHEN=GROUP,BEGIN=(284,8,ZD,EQ,1),PUSH=(292:1,283))
OUTFIL IFOUTLEN=172,INCLUDE=(284,8,ZD,GT,1,AND,
(268,8,CH,LT,567,8,CH,AND,276,8,CH,GT,567,8,CH)),
IFTHEN=(WHEN=(284,8,ZD,EQ,2),BUILD=(292,172,/,1,172))
/*



Can anyone help me out with where i committted error ?
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Fri Dec 11, 2015 12:46 am
Reply with quote

I don't see a reason for writing so many statements in the control card. This should be much simpler than what you have.

You should use KEYBEGIN in the IFTHEN=(WHEN and then use OUTFIL INCLUDE for the condition.

.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Fri Dec 11, 2015 12:50 am
Reply with quote

So, If your test data looks like this:
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
001000399 6064139420 2015-01-01 2015-12-31 00040004
001000399 6064139420 2016-01-01 9999-12-31 00090009
000100137 1094684540 2015-01-01 2015-12-31 00640064
000100137 1094684540 2015-03-03 9999-12-31 00640064
Then, the below control card should work:
Code:
 INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,9),PUSH=(55:1,51))
 OUTFIL INCLUDE=(22,10,CH,GT,76,10,CH,AND,22,10,CH,LT,87,10,CH),
                                 BUILD=(55,51,/,1,51)
 OPTION COPY


.
Back to top
View user's profile Send private message
pecsakthivel123

New User


Joined: 10 Dec 2015
Posts: 2
Location: India

PostPosted: Fri Dec 11, 2015 1:12 am
Reply with quote

Thanks for your answer...

The one you showed is not actual field length.
Please provide based on requested i/p field length
Primary key (Src- ID) is 75 byte length.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2591
Location: NYC,USA

PostPosted: Fri Dec 11, 2015 1:32 am
Reply with quote

Quote:
Please provide based on requested i/p field length

You should modify on the shell He has provided than waiting and asking for the complete 100% solution.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Fri Dec 11, 2015 1:35 am
Reply with quote

Quote:
The one you showed is not actual field length.
Please provide based on requested i/p field length

You must be kidding icon_evil.gif

.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2591
Location: NYC,USA

PostPosted: Fri Dec 11, 2015 1:54 am
Reply with quote

Code:
000100197 1094684540 2015-01-01 2015-11-31 00640064 
000100197 1094684540 2015-11-31 9999-12-31 00640064 

It is better to Code for such scenario to avoid TS coming back regretting and saying incomplete solution.
Code:
22,10,CH,LE,87,10,CH
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

PostPosted: Fri Dec 11, 2015 2:51 am
Reply with quote

No. If they don't know enough to change it themselves, they don't know enough to use it, even if it looks correct on what we've been told.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Selecting multiple constant records DB2 3
This topic is locked: you cannot edit posts or make replies. Writng matched records into output fi... DFSORT/ICETOOL 11
No new posts Building a sort key DFSORT/ICETOOL 3
No new posts Very Large Sort failed - Seeking reco... DFSORT/ICETOOL 7
No new posts Date conversion using SORT SYNCSORT 4
Search our Forums:

Back to Top