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
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
May 25, 2011, 05:17 PM
Francis Mariani
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
May 25, 2011, 05:27 PM
RSquared
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
May 25, 2011, 05:46 PM
FrankDutch
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
May 25, 2011, 08:33 PM
Jim Morrow
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.
Jim Morrow Web Focus 7.6.10 under Windows 2003 MVS 7.3.3
May 26, 2011, 08:46 AM
Jim Conrad
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
May 26, 2011, 09:08 AM
Jim Conrad
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
May 26, 2011, 09:53 AM
rfbowley
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
May 26, 2011, 10:52 AM
Jim Conrad
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
May 26, 2011, 11:02 AM
RSquared
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
May 26, 2011, 11:20 AM
Jim Conrad
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
May 26, 2011, 11:36 AM
Francis Mariani
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
May 26, 2011, 12:04 PM
rfbowley
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
May 26, 2011, 01:04 PM
Jim Conrad
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
May 26, 2011, 02:46 PM
GamP
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
May 26, 2011, 08:19 PM
Waz
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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
May 27, 2011, 03:14 AM
FrankDutch
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
May 27, 2011, 08:22 AM
Jim Conrad
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
May 30, 2011, 05:50 PM
Waz
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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
May 31, 2011, 01:05 PM
Jim Conrad
Thanks to all who made suggestions and helped me solve my problem.