Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Getting Multiple records in output

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

New User


Joined: 31 May 2007
Posts: 31
Location: Chennai, India

PostPosted: Thu May 31, 2007 5:28 pm    Post subject: Getting Multiple records in output
Reply with quote

I have two input files:
File A:
Code:

Cust ID      GURN
00165645                100000000002
00165653                100000000003
00165654                100000000003
00165646                100000000007
00165685                100000000006
00165686                100000000006
00165687                100000000006


File B:
Code:

Account ID        Sort Code    Account No   GURN
1157894042                     10004       15382982       100000000002
1157894042                     10004       15413462       100000000002
1157894042                    504040         662178       100000000002
1157950604                     10004       15435792       100000000006
1157950604                    504044        2220466       100000000006
1538879809                    600706       40567125       100000000016
1538879809                    600706       40572684       100000000016


REQUIREMENT: I need to create output file
1. For every match of GURN on File B (including duplicates) from A, there must be an output record (containing Customer ID & GURN from file A and Sort Code & Account No from file B)
2. If no match is found for any GURN on file B from file A, then the Sortcode & Account must be defaulted to 0s in output file

Output in our example:
Code:

00165645                100000000002   10004       15382982   
00165645                100000000002   10004       15413462   
00165645                100000000002   504040         662178   
00165653                100000000003   000000     00000000
00165654                100000000003   000000     00000000
00165646                100000000007   00000         0000000000
00165685                100000000006   10004       15435792
00165685                100000000006   504044        2220466   
00165686                100000000006   10004       15435792
00165686                100000000006   504044        2220466   
00165687                100000000006   10004       15435792
00165687                100000000006   504044        2220466   



I am unable to multiply the records from file A & file B (as in 100000000006)
Back to top
View user's profile Send private message

murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Thu May 31, 2007 5:50 pm    Post subject:
Reply with quote

Gaurav,

Could you some examples where filea and fileb key matches?
Back to top
View user's profile Send private message
gauravgupta2808
Warnings : 1

New User


Joined: 31 May 2007
Posts: 31
Location: Chennai, India

PostPosted: Thu May 31, 2007 5:59 pm    Post subject:
Reply with quote

Hi,

The key in File A & B is GURN...

I have shown in example:

FILE A

Cust ID GURN
00165645 100000000002
00165653 100000000003
00165654 100000000003
00165646 100000000007
00165685 100000000006
00165686 100000000006
00165687 100000000006

Account ID Sort Code Acount No GURN
1157894042 10004 15382982 100000000002
1157894042 10004 15413462 100000000002
1157894042 504040 662178 100000000002
1157950604 10004 15435792 100000000006
1157950604 504044 2220466 100000000006
1538879809 600706 40567125 100000000016
1538879809 600706 40572684 100000000016
Back to top
View user's profile Send private message
gauravgupta2808
Warnings : 1

New User


Joined: 31 May 2007
Posts: 31
Location: Chennai, India

PostPosted: Thu May 31, 2007 6:41 pm    Post subject:
Reply with quote

Input file Format for better clarity

File A
Field Format Start/End position
Filler X(6)
Cust ID X(20) 7 - 26
GURN S9(12) C-3 27 - 33

File B
Field Format Start/End position
Filler X(6)
Account ID X(20) 7 - 26
Sort Code S9(9) COMP 27 - 30
Account No S9(9) COMP 31 - 34
GURN S9(12) COMP-3 35 - 41

Key - GURN
Back to top
View user's profile Send private message
gauravgupta2808
Warnings : 1

New User


Joined: 31 May 2007
Posts: 31
Location: Chennai, India

PostPosted: Thu May 31, 2007 8:10 pm    Post subject: Using SPLICE for SORT operation
Reply with quote

I have a question about the SPLICE function.

I'm explaing the requirement using an example below:

File 1.
Customer id Sort Code Account number GURN
1157950604 10004 15435792 100000000006
1157950604 504044 2220466 100000000006

File 2 :
Cust ID GURN
00165687 100000000006
00165688 100000000006

The output should be like

Cust Id GURN Retail Sort Code Retail Account

00165687 100000000006 10004 15435792
00165687 100000000006 504044 2220466
00165688 100000000006 10004 15435792
00165688 100000000006 504044 2220466

That means in the output the Cust ID and GURN should come from File 2 while for the same records the retail sort code and account number will be picked up from File 1.

Key is GURN - starts at position 27, length 7 (File 1)
File 2 - starts at position 35, length 7
Back to top
View user's profile Send private message
gauravgupta2808
Warnings : 1

New User


Joined: 31 May 2007
Posts: 31
Location: Chennai, India

PostPosted: Fri Jun 01, 2007 1:43 pm    Post subject:
Reply with quote

I got the solution

This cannot be done using ICETOOL...

ICETOOL is not capable of doing so icon_sad.gif

Write a COBOL Code instead icon_biggrin.gif
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: Fri Jun 01, 2007 10:34 pm    Post subject:
Reply with quote

To clarify: This is a Cartesian join and SPLICE does not have any built-in functions to do that. There is a trick on another board to do a cartesian join with SPLICE, but the rules here do not let me point to that other board, and I'm not sure the trick is very practical anyway.
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 Subtract the ZD fields to provide neg... Balaryan DFSORT/ICETOOL 4 Wed Oct 11, 2017 10:51 pm
No new posts sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts Addition of two records on 2 fields amar143 SYNCSORT 8 Tue Oct 03, 2017 11:14 am
No new posts Where is output from PARM ABEXIT (SNA... packerm CA Products 3 Thu Sep 28, 2017 4:06 pm
No new posts Garbage on output file Hervey Martinez SYNCSORT 4 Wed Sep 27, 2017 12:50 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us