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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Mainframe FOCUS: Many to Many join

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Mainframe FOCUS: Many to Many join
 Login/Join
 
Silver Member
posted
I have two file like:

File 1:
A a
A b
B a
B B


File 2:
A 1
A 2
B 1
B 2

I want to join the two files to get the following output:

A a 1
A a 2
A b 1
A b 2
B a 1
B a 2
B b 1
B b 2

Is this possible via match.

Your suggestions are appreciated.

This message has been edited. Last edited by: Shrikant,


FOCUS 7.2.3
Platform: IBM system Z9 Business class
O/P formats: Flat files, excel and CSV files
 
Posts: 39 | Location: Hyderabad, India | Registered: April 28, 2007Report This Post
Virtuoso
posted Hide Post
Have you even tried a normal join command?
Somthing like: JOIN A IN A TO ALL A IN B AS J1 ??


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Silver Member
posted Hide Post
Yes, I have tried that. As usual it always fails with Duplicates in 'From' field error.


FOCUS 7.2.3
Platform: IBM system Z9 Business class
O/P formats: Flat files, excel and CSV files
 
Posts: 39 | Location: Hyderabad, India | Registered: April 28, 2007Report This Post
Gold member
posted Hide Post
Hi,

Perhaps this will work: if you Join table A to table B hold as C, then B to table A hold as D, then perform a union of C and D. I think in focus you use the MORE command as a union, if not using SQL passthru.

Hope this makes sense! ;-)

Jinx.


7.6.11
Windows
HTML, PDF, Excel etc
DevStudio/Webfocus/Focus IBM
SQL Server 2000 / 2008
DB2
 
Posts: 78 | Location: UK | Registered: February 07, 2008Report This Post
Virtuoso
posted Hide Post
I think that GamP's idea is the way to go. See:

-* Simulate File A
DEFINE FILE CAR
CTR/I4 WITH CAR = CTR + 1;
CODE1/A1 WITH CAR = IF CTR EQ 1 OR 2 THEN 'A' ELSE 'B';
CODE2/A1 WITH CAR = IF CTR EQ 1 OR 3 THEN 'a' ELSE 'b';
END
TABLE FILE CAR
PRINT CODE2
BY CODE1
WHERE RECORDLIMIT EQ 4
ON TABLE HOLD AS A
END

-* Simulate File B
DEFINE FILE CAR
CTR/I4 WITH CAR = CTR + 1;
CODE1/A1 WITH CAR = IF CTR EQ 1 OR 2 THEN 'A' ELSE 'B';
CODE3/I4 WITH CAR = IF CTR EQ 1 OR 3 THEN 1 ELSE 2;
END
TABLE FILE CAR
PRINT CODE3
BY CODE1
WHERE RECORDLIMIT EQ 4
ON TABLE HOLD AS B FORMAT FOCUS INDEX CODE1
END

-*------------------------------------
JOIN CLEAR *
JOIN CODE1 IN A TO ALL CODE1 IN B AS J1

TABLE FILE A
PRINT CODE1
      CODE2
      CODE3
END



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Expert
posted Hide Post
Why not:

JOIN LEFT_OUTER CODE1 IN A TO ALL CODE1 IN B AS J1
-RUN


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Virtuoso
posted Hide Post
quote:
Duplicates in 'From' field error

It always helps if you tell us up front what the situation is: getting errors, type of database used, what did I do already, etc. It saves time, both for you and for us.
This error message tells me that the files you're trying to access are flat files. And these have very strict rules when it comes to joining.
If you convert them to focus files or rdbms files table - whichever has your preference, the error goes away and you get the desired output.
Below code shows exactly that:
FILEDEF MAS_A DISK file_a.mas
FILEDEF MAS_B DISK file_b.mas
FILEDEF FILE_A DISK file_a.ftm
FILEDEF FILE_B DISK file_b.ftm
-RUN
-WRITE MAS_A FILENAME=FILE_A, SUFFIX=FIX, $
-WRITE MAS_A SEGNAME=FILE_A, $
-WRITE MAS_A FIELDNAME=FIELDA1, FORMAT=A1, ACTUAL=A1, $
-WRITE MAS_A FIELDNAME=FIELDA2, FORMAT=A1, ACTUAL=A1, $
-WRITE MAS_B FILENAME=FILE_B, SUFFIX=FIX, $
-WRITE MAS_B SEGNAME=FILE_B, $
-WRITE MAS_B FIELDNAME=FIELDB1, FORMAT=A1, ACTUAL=A1, $
-WRITE MAS_B FIELDNAME=FIELDB2, FORMAT=A1, ACTUAL=A1, $
-WRITE FILE_A Aa
-WRITE FILE_A Ab
-WRITE FILE_A Ba
-WRITE FILE_A Bb
-WRITE FILE_B A1
-WRITE FILE_B A2
-WRITE FILE_B B1
-WRITE FILE_B B2
-RUN

TABLE FILE FILE_A
PRINT *
ON TABLE HOLD AS FA FORMAT FOCUS
END
TABLE FILE FILE_B
PRINT *
ON TABLE HOLD AS FB FORMAT FOCUS INDEX FIELDB1
END
JOIN FIELDA1 IN FA TO ALL FIELDB1 IN FB AS J1
TABLE FILE FA
PRINT FIELDA1
FIELDA2 FIELDB2
END


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Silver Member
posted Hide Post
Thanks GamP

This works perfectly.

The files that I was using were created in previous steps of FOCUS processing.


FOCUS 7.2.3
Platform: IBM system Z9 Business class
O/P formats: Flat files, excel and CSV files
 
Posts: 39 | Location: Hyderabad, India | Registered: April 28, 2007Report 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     [SOLVED] Mainframe FOCUS: Many to Many join

Copyright © 1996-2020 Information Builders