Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

icetool query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
ap_mainframes

Active User


Joined: 29 Dec 2005
Posts: 181
Location: Canada

PostPosted: Tue May 23, 2006 12:11 pm    Post subject: icetool query
Reply with quote

Hi,
I have got a query regarding ICETOOL.
My input file A looks like this

Code:

1100500000995510                                         440.00
1100500000995510                                         441.00
1100500001520586                                         320.00
1100500001407634                                          16.16
1100500001908032                                         648.71
1100500002035622                                         507.29
1100500061003203                                         376.89


and my My input file B looks like this

Code:

1100500000995510                                         ######
1100500001520586                                         !!!!!!
1100500001407634                                         @@@@@@
1100500001908032                                         GGGGGG
1100500002035622                                         &&&&&&
1100500061003203                                         $$$$$$


I am using the JCL as

Code:

//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
COPY FROM(T1) TO(T1OUT)
SPLICE FROM(T1) TO(OUT12) ON(1,16,CH) -
WITHEACH KEEPNODUPS WITH(66,2) USING(CTL3)
//CTL1CNTL DD *
  OUTREC FIELDS=(1,64,65:C'11')
/*
//CTL2CNTL DD *
  OUTREC FIELDS=(1,64,65:C'22')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT12,INCLUDE=(65,2,CH,EQ,C'12'),
  OUTREC(1,66)
/*


and in the output file , I am getting

Code:

1100500001407634                                          16.16
1100500001520586                                         320.00
1100500001908032                                         648.71
1100500002035622                                         507.29
1100500061003203                                         376.89


Please note that 1100500000995510 is common in both the files,
but why is it not appearing in my output file????

Thanks
Back to top
View user's profile Send private message

rajandhla

Active User


Joined: 18 Oct 2005
Posts: 182
Location: Luton UK

PostPosted: Tue May 23, 2006 1:02 pm    Post subject:
Reply with quote

can you please be clear how the output should look like.....
Are looking for the common records in the output file
Back to top
View user's profile Send private message
PascalQ

New User


Joined: 12 May 2006
Posts: 20

PostPosted: Tue May 23, 2006 7:09 pm    Post subject:
Reply with quote

well yeah, you don't say what you want your output to look like.


but using WITHALL instead of WITHEACH does what you asked for.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue May 23, 2006 8:49 pm    Post subject:
Reply with quote

ap,

It's not clear what you're trying to do, but whatever it is, you have things set up wrong. The reason you don't get a 1100500000995510 record is the use of WITHEACH. After the records are sorted on 1,16,CH but before they are spliced, you have this for those three records:

Code:

1100500000995510                                         440.00 11   
1100500000995510                                         441.00 11   
1100500000995510                                         ###### 22   


With WITHEACH WITH(66,2), the WITH field from the second record is spliced into the first record giving 11. Since you only have one WITH field, the third record is ignored. So all you get from SPLICE for these three records is:

Code:

1100500000995510                                         440.00 11


You then delete this record with the OUTFIL INCLUDE for '12'.

If you used WITHALL, you'd get:

Code:

1100500000995510                                         440.00 11 
1100500000995510                                         440.00 12 


You'd then delete the first record and keep the second record with the OUTFIL INCLUDE for '12'.

I have no idea if that's what you want, since you haven't told us what output you're trying to get or the rules for getting it.

Also, you should be using WITH(65,1) rather than WITH(66,2) although that won't affect the output.

Let me know what you're trying to do, preferably with a good example of input and output records including matching and non-matching cases, and I'll show you how to do it correctly.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us