I am having difficulty joining three tables I created from different oracle databases. The all are created as focus tables indexed by the same fields.

Example:

TABLE FILE SOURCE1
PRINT
FIELD1
BY KEY1
BY KEY2
BY KEY3
ON TABLE HOLD AS HOLDFILE1 FORMAT FOCUS INDEX KEY1 KEY2 KEY3
END

TABLE FILE SOURCE2
PRINT
FIELD2
BY KEY1
BY KEY2
BY KEY3
ON TABLE HOLD AS HOLDFILE2 FORMAT FOCUS INDEX KEY1 KEY2 KEY3
END

TABLE FILE SOURCE3
PRINT
FIELD3
BY KEY1
BY KEY2
BY KEY3
ON TABLE HOLD AS HOLDFILE3 FORMAT FOCUS INDEX KEY1 KEY2 KEY3
END


JOIN KEY1 AND KEY2 AND KEY3 IN HOLDFILE1 TO
KEY1 AND KEY2 AND KEY3 IN HOLDFILE2 AS JOIN1 END

JOIN KEY1 AND KEY2 AND KEY3 IN HOLDFILE1 TO
KEY1 AND KEY2 AND KEY3 IN HOLDFILE3 AS JOIN2 END

TABLE FILE HOLDFILE1
PRINT
FIELD1
FIELD2
FIELD3
BY KEY1
BY KEY2
BY KEY3
ON TABLE HOLD AS RESULTTABLE FORMAT FOCUS
END

I receve the following message:

(FOC236) LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT: HOLDFILE2

I am new to this version and a lot of what I did in 5.2.1 doesn't work the same. I have to review and change most FEXes. What am I doing wrong?

I tried this in both dev studio (713) and MRE.

I am using Webfocus 7.1.3. The reporting server is UNIX/AIX and the webserver is a Windows 2003 machine.


Thanks in advance for any suggestions.
Tony

quote:

JOIN KEY1 AND KEY2 AND KEY3 IN HOLDFILE1 TO
KEY1 AND KEY2 AND KEY3 IN HOLDFILE2 AS JOIN1 END

JOIN KEY1 AND KEY2 AND KEY3 IN HOLDFILE1 TO
KEY1 AND KEY2 AND KEY3 IN HOLDFILE3 AS JOIN2 END

Don't hold files much as focus types, but try a minor adjustment to your joins to put 'join' on one line by itself and 'end' on one line by itself.

Do you have to hold as focus? Also, you may need to tag the fields.

Other thought do all the tables have data in them when passed, might need to set emptyreport on

The FOCUS datafile doesn't support multi-field joins.

You can join from multiple fields to a single (or group) field. This is where the SQL tables have an advantage.

You can create a one field concatenation of the fields in your HOLDFILE2/3 and then index that field and then join the 3 fields to it, or concatenate in all threee files and join from /to a single field.

if the fields in the 3 holdfiles are in fact the same, you should not use join.

TABLE FILE HOLDFILE1
PRINT FIELD1 FIELD2 FIELD3
BY KEY1
BY KEY2
BY KEY3
MORE
FILE HOLDFILE2
MORE
FILE HOLDFILE3
END

If the fields in the 3 holdfiles are different (so in fact FIELD4 etc, then you should either follow one of the earlier mentioned solutions or use the MATCH command.

Frank

Thanks to all.

I should have thought of the "more" solution.
That works for me.
Tony

Why don't you do the join on the Oracle tables? Why do you need to create hold files at all. Let Oracle do the work.