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,

Jim,

Can you print out your code?

Thank you

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

Is it possible that LEFT_OUTER does not work with non-keyed files (HOLD FORMAT ALPHA/BINARY..)? Try holding them as FORMAT FOCUS.

Did you do a SET ALL= ON before your join ? Try it and let us know.

instead of joining the files look at the possibilities you have with the command MATCH

ON MATCH HOLD OLD-OR-NEW

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.

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.

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?

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

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...

Can you please dump the data to show us what is really in the 2 hold files?

Thank you

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.

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 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.

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!

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.

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

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

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.

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.

Thanks to all who made suggestions and helped me solve my problem.