So I'm feeling a bit embarrassed as I've done this hundreds of times, but I went to put together a quick proof of concept of the McGyver Technique to help someone with an issue... And I can't get it to display properly. Can someone tell me what I'm doing wrong, because pretty sure this would have worked pre wf8.

DEFINE FILE CAR
BLANK/A1 WITH COUNTRY='1';
END
TABLE FILE CAR
SUM SALES RETAIL_COST
BY COUNTRY
BY BLANK
ON TABLE HOLD AS MY_HOLD FORMAT ALPHA
END


TABLE FILE MY_HOLD
SUM SALES RETAIL_COST
BY BLANK
BY COUNTRY
ON TABLE HOLD AS MY_HOLD2 FORMAT ALPHA
END

JOIN
INNER MY_HOLD.MY_HOLD.BLANK IN MY_HOLD TAG H1 TO MULTIPLE
MY_HOLD2.MY_HOLD2.BLANK IN MY_HOLD2 TAG H2 AS J0
END

TABLE FILE MY_HOLD
SUM H2.SALES H2.RETAIL_COST
BY H1.COUNTRY
BY H2.COUNTRY
ON TABLE PCHOLD FORMAT HTML
END  

For this result set I would expect to get 25 rows, instead I'm getting 5. England in H1.COUNTRY and then each country in H2.

PAGE 1 
 
COUNTRY COUNTRY SALES RETAIL_COST 
ENGLAND ENGLAND 12000 45,319 
        FRANCE   0    5,610 
        ITALY   30200 51,065 
        JAPAN   78030 6,478 
        W GERMANY 88190 64,732 
  

When I look at the error messages, I'm getting the below error for each value after England.

quote:

(FOC1072) DUPLICATES IN JOIN 'FROM' FIELD : MY_HOLD2/

I thought FOC1072 was just a warning and should allow me to create my cartesian join. What did I forget about? or is this a result of code tightening?

This message has been edited. Last edited by: eric.woerle,

DEFINE FILE CAR
BLANK/A1 WITH COUNTRY='1';
END
-*
TABLE FILE CAR
SUM SALES RETAIL_COST
BY BLANK
BY COUNTRY
ON TABLE HOLD AS MY_HOLD FORMAT ALPHA
END
-*
TABLE FILE MY_HOLD
SUM SALES RETAIL_COST
BY BLANK
BY COUNTRY
ON TABLE HOLD AS MY_HOLD2 FORMAT FOCUS INDEX BLANK
END
-*
JOIN
BLANK IN MY_HOLD TAG H1 TO ALL
BLANK IN MY_HOLD2 TAG H2 AS J0
END
-*
TABLE FILE MY_HOLD
SUM H2.SALES H2.RETAIL_COST
BY H1.COUNTRY
BY H2.COUNTRY
ON TABLE PCHOLD FORMAT HTML
END    

Yields 25 rows.

Basically, I only changed the cross-reference file from physical sequential to indexed.

If you need/want the cross-reference file to be physical sequential, you'll usually see parent/child attributes in the .mas.

See for example:
http://forums.informationbuild...707040786#3707040786

This message has been edited. Last edited by: David Briars,

I so much prefer SQL statements for this.

More control, and no pesky index, alpha or whatever. Just focus files.

DEFINE FILE CAR
BLANK/A1 WITH COUNTRY='1';
END

TABLE FILE CAR
SUM SALES RETAIL_COST
BY	BLANK
BY COUNTRY
ON TABLE HOLD AS H01
END
-*
TABLE FILE CAR
SUM SALES RETAIL_COST
BY	BLANK
BY COUNTRY
ON TABLE HOLD AS H02
END

SQL
SELECT			H01.*,
				H02.*
FROM			H01
INNER JOIN		H02
ON	H01.BLANK = H02.BLANK
;
TABLE HOLD AS H_CARTESIAN
END

TABLE FILE H_CARTESIAN
PRINT *
ON TABLE PCHOLD FORMAT HTML
END    

And with "control" I mean. Consider these extra join-conditions

All joins expect with own : AND H01.COUNTRY <> H02.COUNTRY

no double records ( e.g. only alphabetically higher countries ) : AND H01.COUNTRY < H02.COUNTRY



Greets,

Better yet, in SQL you do not need the BLANK column as the default join is a cartesian product:

TABLE FILE CAR
SUM SALES RETAIL_COST
BY COUNTRY
ON TABLE HOLD AS H01
END

TABLE FILE CAR
SUM SALES RETAIL_COST
BY COUNTRY
ON TABLE HOLD AS H02
END

SQL
SELECT	H01.*, H02.*
FROM	H01, H02
;
TABLE HOLD AS H_CARTESIAN
END

TABLE FILE H_CARTESIAN
PRINT *
ON TABLE PCHOLD FORMAT HTML
END

quote:

Basically, I only changed the cross-reference file from physical sequential to indexed.

Thought I tried that because I saw that mentioned in another post. I guess I missed something. Thanks David!.

I could have sworn back in 7.x you didn't need to do that though. Maybe its just code tightening.