Getting Multiple records in output

gauravgupta2808 · Posted: Thu May 31, 2007 5:28 pm

I have two input files:
File A:

murmohk1 · Posted: Thu May 31, 2007 5:50 pm

Gaurav,

Could you some examples where filea and fileb key matches?

gauravgupta2808 · Posted: Thu May 31, 2007 5:59 pm

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

gauravgupta2808 · Posted: Thu May 31, 2007 6:41 pm

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

gauravgupta2808 · Posted: Thu May 31, 2007 8:10 pm

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

gauravgupta2808 · Posted: Fri Jun 01, 2007 1:43 pm

I got the solution

This cannot be done using ICETOOL...

ICETOOL is not capable of doing so

Write a COBOL Code instead

Frank Yaeger · Posted: Fri Jun 01, 2007 10:34 pm

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.