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] Many-to-Many (Cartesian product)
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Many-to-Many (Cartesian product)
 Login/Join
 
Member
posted
I have two HOLD files that I need to join so I can get a many-to-many result. Instead, WF appears to join all the rows in Table2 with the first matching row in Table1, then I get the next row from Table1 (with the same key value) with no data from Table2.

JOIN LEFT_OUTER
KEY IN TABLE1 TO ALL
KEY IN TABLE2
END

Table 1 Table 2
Key Key FldX
--- --- ----
AAA AAA ABC
AAA AAA DEF
BBB AAA GHI
CCC

Needed Output
AAA AAA ABC
AAA AAA DEF
AAA AAA GHI
AAA AAA ABC
AAA AAA DEF
AAA AAA GHI
BBB
CCC

Getting Instead
AAA AAA ABC
AAA AAA DEF
AAA AAA GHI
AAA
BBB
CCC


Suggestions?

This message has been edited. Last edited by: Jim Conrad,


WebFOCUS 7.6.11
Windows, All Outputs
 
Posts: 15 | Registered: May 24, 2011Reply With QuoteReport This Post
Guru
posted Hide Post
Jim,

Can you print out your code?

Thank you


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Reply With QuoteReport This Post
Member
posted Hide Post
The entire code is pages and pages. But the bottom line is like this:
TABLE FILE FILE1
PRINT
Fld1 Fld2
BY Fld3
ON TABLE HOLD AS HLD1
END

TABLE FILE FILE2
PRINT
FldX FldY
By FldZ
ON TABLE HOLD AS HLD2
END

JOIN LEFT_OUTER
Fld3 IN HLD1 TO ALL
FldZ IN HLD2
END

TABLE FILE HLD1
PRINT
Fld1 Fld2 Fld3 FldX FldY FldZ
END

Note that both FILE1 and FILE2 will have multiple rows where FILE1.Fld3 = "AAA" and FILE2.FldZ = "AAA".

The first AAA in FILE1 is being matched to each of the AAA rows in FILE2.

Assuming that there are two AAA rows in FILE1 and two AAA rows in FILE2, I want four rows in my output. Currently I am getting three.

AAA (from F1) AAA (from F2) Other stuff from F2
AAA (from F1) AAA (from 2nd row in F2)
AAA (from 2nd row in F1) with nothing from F2


WebFOCUS 7.6.11
Windows, All Outputs
 
Posts: 15 | Registered: May 24, 2011Reply With QuoteReport This Post
Expert
posted Hide Post
Is it possible that LEFT_OUTER does not work with non-keyed files (HOLD FORMAT ALPHA/BINARY..)? Try holding them as FORMAT FOCUS.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
Did you do a SET ALL= ON before your join ? Try it and let us know.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
instead of joining the files look at the possibilities you have with the command MATCH

ON MATCH HOLD OLD-OR-NEW




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, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
A "flat" file join is actually a synchronized read of the two files. This gives them a behavior which is slightly different from "SQL" joins. I agree explore making both FOCUS files, and / or "MATCH FILE"


Of course you know if it was the end of the day. and you quickly created a report from the joined files, and rushed it off to "senior management", before going home. You would discover the next morning you had done a Cartesian product. Smiler


Jim Morrow
Web Focus 7.6.10 under Windows 2003
MVS 7.3.3



 
Posts: 129 | Registered: June 01, 2005Reply With QuoteReport This Post
Member
posted Hide Post
Added:
SET CARTESIAN = ON
SET ALL = ON

Changed each ON TABLE HOLD to
ON TABLE HOLD FORMAT FOCUS INDEX Fld3
And
ON TABLE HOLD FORMAT FOCUS INDEX FldZ

And... the results are the same as before.

Changed the JOIN, dropping the LEFT_OUTER

Still the same output.


WebFOCUS 7.6.11
Windows, All Outputs
 
Posts: 15 | Registered: May 24, 2011Reply With QuoteReport This Post
Member
posted Hide Post
MATCH doesn't work either. I get similar results to the JOIN tried above.

Is it possible that we have discovered something that WebFOCUS cannot handle?


WebFOCUS 7.6.11
Windows, All Outputs
 
Posts: 15 | Registered: May 24, 2011Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Maybe it is just me, but I would suggest a modification of the word 'ALL' in your join to 'MULTIPLE'

JOIN LEFT_OUTER
Fld3 IN HLD1 TO ALL
FldZ IN HLD2
END


becomes


JOIN LEFT_OUTER
Fld3 IN HLD1 TO MULTIPLE
FldZ IN HLD2
END


I have never had good results using the SQL 'ALL' in a FOCUS join


Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001
 
Posts: 132 | Location: Gadsden, Al | Registered: July 22, 2005Reply With QuoteReport This Post
Member
posted Hide Post
ALL and MULTIPLE should produce the same results. And unfortunately in this case, they do. Still am not getting the many-to-many I need.

Keep the suggestions coming...


WebFOCUS 7.6.11
Windows, All Outputs
 
Posts: 15 | Registered: May 24, 2011Reply With QuoteReport This Post
Guru
posted Hide Post
Can you please dump the data to show us what is really in the 2 hold files?

Thank you


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Reply With QuoteReport This Post
Member
posted Hide Post
Sorry, but the data is private, so I can't dump it.

But look at my first post in this chain for some made-up examples.


WebFOCUS 7.6.11
Windows, All Outputs
 
Posts: 15 | Registered: May 24, 2011Reply With QuoteReport This Post
Expert
posted Hide Post
We should be working with a reproducible example using one of the demo files.

Here's one. It first creates two FOCUS DB HOLD files, one that contains COUNTRY rows - 2 for each COUNTRY, and the second that contains 1 data column, CAR, for each COUNTRY. The output from joining them together contains the data column value for each of the COUNTRY rows:

SET HOLDLIST=PRINTONLY

TABLE FILE CAR
PRINT
COUNTRY NOPRINT
BY COUNTRY
ON TABLE HOLD AS H001 FORMAT FOCUS INDEX COUNTRY
MORE
FILE CAR
END

TABLE FILE CAR
SUM
CAR
BY COUNTRY
ON TABLE HOLD AS H002 FORMAT FOCUS INDEX COUNTRY
END

JOIN COUNTRY IN H001 TO ALL COUNTRY IN H002

TABLE FILE H001
PRINT
COUNTRY
CAR
ON TABLE SET STYLEMODE FIXED
END

File 1:
 COUNTRY
 -------
 ENGLAND
 ENGLAND
 FRANCE
 FRANCE
 ITALY
 ITALY
 JAPAN
 JAPAN
 W GERMANY
 W GERMANY

File 2:
 COUNTRY     CAR
 -------     ---
 ENGLAND     TRIUMPH
 FRANCE      PEUGEOT
 ITALY       MASERATI
 JAPAN       TOYOTA
 W GERMANY   BMW

Result:
 
 COUNTRY     CAR
 -------     ---
 ENGLAND     TRIUMPH
 ENGLAND     TRIUMPH
 FRANCE      PEUGEOT
 FRANCE      PEUGEOT
 ITALY       MASERATI
 ITALY       MASERATI
 JAPAN       TOYOTA
 JAPAN       TOYOTA
 W GERMANY   BMW
 W GERMANY   BMW

Please describe how your scenario is different from this one.

This message has been edited. Last edited by: Francis Mariani,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Francis got it right when he specified to make th eintermediat hold FOCUS file rather than ALPHA's

It has to do with how FOCUS reads text files. As I understand it FOCUS can only make one pass through a text file and when it reaches the end, that is it.

I ran the following and got exactly the output you reported:

DEFINE FILE CAR
	FACT1/A3 = DECODE COUNTRY (ENGLAND 'AAA' FRANCE 'AAA' ITALY 'BBB'  JAPAN 'CCC');
END

TABLE FILE CAR
PRINT 
	FACT1
BY COUNTRY NOPRINT
WHERE COUNTRY NE 'W GERMANY'
ON TABLE HOLD AS STEP1 
END
-RUN

JOIN CLEAR *

DEFINE FILE CAR
	FACT2/A3 = 'AAA';
	FACT3/A3 = DECODE COUNTRY (ENGLAND 'ABC' FRANCE 'DEF' ITALY 'GHI' );
END

TABLE FILE CAR
PRINT 
	FACT2
	FACT3
BY COUNTRY NOPRINT
WHERE COUNTRY NE 'W GERMANY'
WHERE COUNTRY NE 'JAPAN'

ON TABLE HOLD AS STEP2 
END
-RUN

JOIN CLEAR *

JOIN LEFT_OUTER FACT1 IN STEP1 TO MULTIPLE FACT2 IN STEP2 AS J0

TABLE FILE STEP1
PRINT 
	FACT1
	FACT2
	FACT3
END
-RUN


However changing the hold in step2 to:

ON TABLE HOLD AS STEP2 FORMAT FOCUS INDEX FACT2


and the result is exactly what you wanted.


Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001
 
Posts: 132 | Location: Gadsden, Al | Registered: July 22, 2005Reply With QuoteReport This Post
Member
posted Hide Post
I accidentally misrepresented my original case. Rather than a single field being joined, I actually have two fields.

JOIN Fld1 AND Fld2 IN HLD1 TO ALL FldX AND FldY IN HLD2

This apparently does NOT work. So I changed the program to build a concatenated key for each HOLD file (in FOCUS format) and join just the single field. AND IT WORKS!

Thanks to all who spent time helping me thru this!


WebFOCUS 7.6.11
Windows, All Outputs
 
Posts: 15 | Registered: May 24, 2011Reply With QuoteReport This Post
Virtuoso
posted Hide Post
And then it makes some more sense of course, since you can't do multi-field joins for focus files. That's a relational thing only. So by making it a single field join it works as expected.


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
Expert
posted Hide Post
Just to confuse everyone, you could also try a conditional join.

TABLE FILE VIDEOTRK
 SUM LASTNAME
 BY MOVIECODE
 ON TABLE HOLD AS HOLD1
END

TABLE FILE MOVIES
 SUM TITLE
 BY RELDATE
 ON TABLE HOLD AS HOLD2
END

JOIN FILE HOLD1 AT MOVIECODE TAG V1 TO ALL 
     FILE HOLD2 AT RELDATE   TAG M1 AS JW1
  WHERE V1.MOVIECODE EQ M1.MOVIECODE;
END
TABLE FILE HOLD1
 PRINT LASTNAME
 TITLE
END


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6293 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
JOIN Fld1 AND Fld2 IN HLD1 TO ALL FldX AND FldY IN HLD2


Create a dummy keyfield as a combination of Fld1 and Fld2

DEFINE FILE HLD1
NEWKEY/A10=Fld1||Fld2;
END
DEFINE FILE HLD2
NEWKEY2/A10=FLDx||FLDY;
END

JOIN NEWKEY1 IN HLD1 TO ALL NEWKEY2 IN HLD2


But I am a bit confused by your question and your own remarks on this
You DO want a Kartesian product or you do NOT want a Kartesian product??

The join statement will give you the intersection and NOT a Kartesian product.

When you do a match you will get the Kartesian result.

MATCH FILE HLD1
PRINT FIELDS
BY NEWKEY1
RUN
FILE HLD2
PRINT FIELDS2
BY NEWKEY2
AFTER MATCH HOLD OLD-OR-NEW
END




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, 2006Reply With QuoteReport This Post
Member
posted Hide Post
I DO want a Cartesian product (I need a many-to-many join).

Holding the two extracts in FOCUS format, with a single concatenated key worked perfectly.


WebFOCUS 7.6.11
Windows, All Outputs
 
Posts: 15 | Registered: May 24, 2011Reply With QuoteReport This Post
Expert
posted Hide Post
Your Host file does not need to be an Indexed FOCUS file, the indes is only needed on the join file.

You can get performance improvements by not creating a FOCUS file for the Host file.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6293 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
Thanks to all who made suggestions and helped me solve my problem.


WebFOCUS 7.6.11
Windows, All Outputs
 
Posts: 15 | Registered: May 24, 2011Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Many-to-Many (Cartesian product)

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