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

Hookup group of records in multiple places in input dataset


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 216
Location: USA

PostPosted: Thu Feb 18, 2016 11:49 pm
Reply with quote

Hi All,

I have input dataset with SQL query as below. LRECL=80, RECFM=FB

Code:
 

SELECT * FROM A WHERE A1 IN (
)

UNION

SELECT * FROM B WHERE B1 IN (
)

UNION

SELECT * FROM C WHERE C1 IN(
)

WITH UR;

 



I have second dataset that contains list of keys that need to be hooked in each IN clause as below. LRECL=80,RECFM=FB

Code:


 '1','2','3','4','5'
,'12','16','17',18'
,'19','20'
 


This group of records should go into every IN clause of the above query.

Can it be possible to do using SORT?

I am planning to do as below.

1.count the IN clauses in the input query. The number of IN clauses can vary, they are not 3 always as shown above.
2.Repeat the group of records in second dataset based on the count from step 1. Have each group of records tagged with a number and alternate the number for every next group, such as 2,4,6,8 etc...
3.For the query put a tag like <A> from start of the query and the next will be after the IN clause. Essentially group the records will have the same sequence number before IN and after IN clause and alternate way as in 1,3,5,7 etc...
4.Now, copy the query and the repeated group of records and sort by the number attached. This should rearrange the records and prepare as below.

Code:


SELECT * FROM A WHERE A1 IN (
 '1','2','3','4','5'
,'12','16','17',18'
,'19','20'
)

UNION

SELECT * FROM B WHERE B1 IN (
 '1','2','3','4','5'
,'12','16','17',18'
,'19','20'
)

UNION

SELECT * FROM C WHERE C1 IN(
 '1','2','3','4','5'
,'12','16','17',18'
,'19','20'
)

WITH UR;

 



Please suggest me if there is any better way to do this. Thank you very much for the help.

Regards
Back to top
View user's profile Send private message
kranthikumarb

Active User


Joined: 02 Jan 2009
Posts: 115
Location: Hyderabad

PostPosted: Fri Feb 19, 2016 12:12 pm
Reply with quote

Does the number of queries change every time or is it constant? Do you always have 3 or n number of queries in your dataset?
Back to top
View user's profile Send private message
kranthikumarb

Active User


Joined: 02 Jan 2009
Posts: 115
Location: Hyderabad

PostPosted: Fri Feb 19, 2016 1:22 pm
Reply with quote

Assuming your table/select statements count doesn't change, here is some idea.

File1 has only the below
Code:

SELECT * FROM A WHERE A1 IN (                                     
)                                                                 
UNION


File2 has the below

Code:

 '1','2','3','4','5'                                             
,'12','16','17','18'                                             
,'19','20'


Code:

//SUMKK    EXEC PGM=ICETOOL                                       
//SEND     DD   DSN=&&SEND,UNIT=SYSDA,SPACE=(CYL,(250,100)),     
//             DISP=(,PASS)                                       
//CATCH    DD   DSN=*.SEND,DISP=(OLD,PASS),VOL=REF=*.SEND         
//TOOLMSG  DD   SYSOUT=*                                         
//DFSMSG   DD   SYSOUT=*                                         
//SORTIN   DD DSN=file1                                                   
                 DD DSN=file2
//TOOLIN   DD   *                                                 
  COPY FROM(SORTIN) TO(SEND) USING(CTL1)                         
  COPY   FROM(SEND) TO(SORTOUT) USING(CTL2)                       
//*                                                               
//CTL1CNTL DD   *                                                 
  SORT FIELDS=COPY                                               
  INREC IFTHEN=(WHEN=(1,1,CH,EQ,C'S'),OVERLAY=(53:C'1')),           
        IFTHEN=(WHEN=(1,1,CH,EQ,C' '),OVERLAY=(53:C'2')),           
        IFTHEN=(WHEN=(1,1,CH,EQ,C','),OVERLAY=(53:C'3')),           
        IFTHEN=(WHEN=(1,1,CH,EQ,C')'),OVERLAY=(53:C'8')),           
        IFTHEN=(WHEN=(1,1,CH,EQ,C'U'),OVERLAY=(53:C'9'))           
  OUTFIL REPEAT=3,OVERLAY=(50:SEQNUM,3,ZD,RESTART=(1,10))           
/*                                                                 
//CTL2CNTL DD   *                                                   
  SORT FIELDS=(50,4,CH,A)                                           
  OUTREC IFTHEN=(WHEN=(50,3,CH,EQ,C'002',AND,1,1,CH,EQ,C'S'),       
                    OVERLAY=(15:C'B',23:C'B')),                     
         IFTHEN=(WHEN=(50,3,CH,EQ,C'003',AND,1,1,CH,EQ,C'S'),       
                    OVERLAY=(15:C'C',23:C'C'))                     
  OUTFIL FNAMES=SORTOUT,OMIT=(1,1,CH,EQ,C'U',AND,50,3,CH,EQ,C'003'),
    BUILD=(1,49),TRAILER1=('WITH UR;')                             
//SORTOUT  DD   SYSOUT=*                                           



This may not be the best. But see if it helps you in any way.
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 216
Location: USA

PostPosted: Fri Feb 19, 2016 9:35 pm
Reply with quote

Hi Kranthi,

Thank you for your post.

The number of queries are variable and the IN clauses within each query are variable. Means the example query I posted has 3 IN clauses. In the next query I might have only one IN clause. so, I will need to add the records only once.

Regards
Amar
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 INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts FINDREP - Only first record from give... DFSORT/ICETOOL 3
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
Search our Forums:

Back to Top