March 05, 2007, 03:03 PM
TonyDarmaninJoining created focus tables
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
March 05, 2007, 03:22 PM
Leahquote:
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
March 05, 2007, 03:37 PM
Alan BThe 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.
March 05, 2007, 03:51 PM
FrankDutchif 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
March 05, 2007, 04:10 PM
TonyDarmaninThanks to all.
I should have thought of the "more" solution.
That works for me.
Tony
March 06, 2007, 05:03 AM
OPALTOSHWhy 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.