Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Inner Join with repeating values

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Inner Join with repeating values
 Login/Join
 
Gold member
posted
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
 
Posts: 93 | Registered: February 20, 2008Report This Post
Virtuoso
posted Hide Post
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.


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
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Virtuoso
posted Hide Post
And 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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Gold member
posted Hide Post
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


WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8
 
Posts: 93 | Registered: February 20, 2008Report This Post
Gold member
posted Hide Post
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.


WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8
 
Posts: 93 | Registered: February 20, 2008Report This Post
Virtuoso
posted Hide Post
Change 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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Gold member
posted Hide Post
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?


WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8
 
Posts: 93 | Registered: February 20, 2008Report This Post
Expert
posted Hide Post
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



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 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Virtuoso
posted Hide Post
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.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Master
posted Hide Post
Have you tried doing a match?


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Gold member
posted Hide Post
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 Smiler


WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8
 
Posts: 93 | Registered: February 20, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Inner Join with repeating values

Copyright © 1996-2020 Information Builders