I have a problem. I am joining two HOLD tables with a common customer ID. I'm using inner join because I want results where the customer is in both tables. Table 1 has one account type and table 2 has a different account type. A customer may have more than one account in either table. In cases where a customer has more that one account type in one of the tables but only one account in the other, I get:
------------------------------------------
CUSTOMER ACCT(TABLE1) ACCT(TABLE2)
123 11111 2222222
123 33333
456 44444 5555555
789 66666 7777777
789 8888888
------------------------------------------
I want:
------------------------------------------
CUSTOMER ACCT(TABLE1) ACCT(TABLE2)
123 11111 2222222
123 33333 2222222
456 44444 5555555
789 66666 7777777
789 66666 8888888
------------------------------------------

Any way to do this?

Thanks.

We would probably need to see your code to see how you are getting the results you show. You should be seeing results like what you are wanting. There must be something in your code that is changing the desired result - esp that last record where you are wanting 8888888 in table2 field and it is showing in table1 field. Maybe try adding fully qualified fieldnames (tablename.segment.field) to make sure you are getting what you think you are getting.

And show us how you created the hold files.
What is the type?
Do you use index fields?

Darin,
the last one '888888' is in TABLE2, not TABLE1. The text on the forum post shifted it over. Here is code I'm using:
SET ALL = OFF
JOIN CLEAR *
JOIN
INNER CUST.CUST.CUST_ID IN CUST TO MULTIPLE DDA.DDA.CUST_ID IN DDA AS J2
END
JOIN
CUST.CUST.CUST_ID IN CUST TO MULTIPLE MM.MM.CUST_ID IN MM
AS J3
END
TABLE FILE CUST
PRINT
'CUST.CUST.CUST_ID' AS 'CUST_ID'
'DDA.DDA.CHK_ACCT_ID' AS 'CHK_ACCT_ID'
'DDA.DDA.CHK_SUB_PRD' AS 'CHK_SUB_PRD'
'MM.MM.MM_ACCT_ID' AS 'MM_ACCT_ID'
ON TABLE HOLD AS ALL_ACCTS FORMAT DB2
END

quote:

Originally posted by FrankDutch:
And show us how you created the hold files.
What is the type?
Do you use index fields?

CUST hold file is a flat file summed and sorted by CUST_ID. Other holds are flat files printed and sorted by CUST_ID.

Change your hold files to Focus format and add an index.

ON TABLE HOLD AS FILE1 FORMAT FOCUS INDEX CUSTOMER

I changed all hold file for FOCUS hold index CUST_ID. Still getting same results. I also set ALL = OFF. Should I have only held the CUST as focus hold, or the other two used as cross reference?

Bob,

You could get the result you want by changing the "join_from" field in your second join to be the key field in the second table -
e.g. JOIN DDA.DDA.CUST_ID IN CUST TO MULTIPLE MM.MM.CUST_ID IN MM AS J3
but I would hazard a guess that there could be situations where an account number that occurs in file 2 does not appear in table 1, so that is probably not the best way forward for you.

The alternative is to COMPUTE the values to be the same as the last value if the CUST_ID hasn't changed and the ACCT is missing.

EX -LINES 3 EDAPUT MASTER,CUSTACCT,CF,MEM,FILENAME=CUSTACCT, SUFFIX=FOC,$
SEGNAME=ONE, SEGTYPE=S1 ,$
  FIELD=CUST_ID, ,A4   ,A4   ,FIELDTYPE=I, $
-RUN
CREATE FILE CUSTACCT
MODIFY FILE CUSTACCT
FIXFORM CUST_ID/A4
DATA
123
456
789
END
-RUN
EX -LINES 4 EDAPUT MASTER,CUST1,CF,MEM,FILENAME=CUST1, SUFFIX=FOC,$
SEGNAME=ONE, SEGTYPE=S2 ,$
  FIELD=CUST_ID, ,A4   ,A4   ,FIELDTYPE=I, $
  FIELD=ACCT,    ,A8   ,A8   ,$
-RUN
CREATE FILE CUST1
MODIFY FILE CUST1
FIXFORM CUST_ID/A4 ACCT/A8
DATA
123 11111
123 33333
456 44444
789 66666
END
-RUN
EX -LINES 4 EDAPUT MASTER,CUST2,CF,MEM,FILENAME=CUST2, SUFFIX=FOC,$
SEGNAME=ONE, SEGTYPE=S2 ,$
  FIELD=CUST_ID, ,A4   ,A4   ,FIELDTYPE=I, $
  FIELD=ACCT,    ,A8   ,A8   ,$
-RUN
CREATE FILE CUST2
MODIFY FILE CUST2
FIXFORM CUST_ID/A4 ACCT/A8
DATA
123 2222222
456 5555555
789 7777777
789 8888888
END
-RUN
JOIN CLEAR *
JOIN CUST_ID IN CUSTACCT TO MULTIPLE CUST_ID IN CUST1 TAG T1 AS J1
JOIN CUST_ID IN CUSTACCT TO MULTIPLE CUST_ID IN CUST2 TAG T2 AS J2
TABLE FILE CUSTACCT
PRINT COMPUTE ACCT1/A8 = IF CUST_ID NE LAST CUST_ID THEN T1.ACCT
                    ELSE IF T1.ACCT EQ '' THEN LAST T1.ACCT ELSE T1.ACCT;
      COMPUTE ACCT2/A8 = IF CUST_ID NE LAST CUST_ID THEN T2.ACCT
                    ELSE IF T2.ACCT EQ '' THEN LAST T2.ACCT ELSE T2.ACCT;
-* This will show what you would have without the COMPUTE statements above
      T1.ACCT
      T2.ACCT
   BY CUST_ID
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF, SIZE=9, $
ENDSTYLE
END

I have added the creation of the files so that others can use this example to understand how it functions and also see the result for themselves.

T

Your spec is incomplete; it all depends on the precise requirements.

What output should be produced if a cust had several accounts of each type, but unequal numbers?

For instance

123 11111
123 11112
123 11113

in file one, and

123 2222221
123 2222222

in file two.

Have you tried doing a match?

Thank you Tony. The COMPUTE you suggested worked! I just went from the JOIN, TABLE, COMPUTE you suggested. I did not do anything with CREATE, MODIFY, FIXFORM...

Thanks to all who replied with suggestions. I really appreciate it.

Robert