Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Mainframe FOCUS: Many to Many join
Go
New
Search
Notify
Tools
Reply
  
[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, 2007Reply With QuoteReport 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: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport 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, 2007Reply With QuoteReport 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, 2008Reply With QuoteReport 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, 2005Reply With QuoteReport 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: 1973 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport 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: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport 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, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

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

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.