Focal Point
Inner Join with repeating values
January 14, 2009, 01:18 PM
BobVInner Join with repeating values
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.
WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8
January 14, 2009, 01:25 PM
Darin LeeWe 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.
Regards,
Darin
In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
January 14, 2009, 01:40 PM
FrankDutchAnd show us how you created the hold files.
What is the type?
Do you use index fields?
|
Frank |
| prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
January 14, 2009, 01:42 PM
BobVDarin,
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
WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8
January 14, 2009, 01:57 PM
BobVquote:
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.
WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8
January 14, 2009, 02:52 PM
FrankDutchChange your hold files to Focus format and add an index.
ON TABLE HOLD AS FILE1 FORMAT FOCUS INDEX CUSTOMER
|
Frank |
| prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
January 14, 2009, 06:41 PM
BobVI 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?
WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8
January 15, 2009, 02:38 AM
Tony ABob,
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
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | | |
WebFOCUS App Studio 8.2.06 standalone on Windows 10 | |
January 15, 2009, 11:27 AM
j.grossYour 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.
- Jack Gross
WF through 8.1.05
January 15, 2009, 12:09 PM
PBrightwellHave you tried doing a match?
Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
January 15, 2009, 12:32 PM
BobVThank 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
WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8