I feel like I should know the answer to this question, but the only answer I can come up with is "No", and that surprises me!

I want to do WebFocus joins between three tables, not table A to table B and table A to table C, but table B to table C and table A to table B. Specifically, I'm reporting off Oracle tables and all columns are not indexed (and I can't change that).

I know I can do this:

C
^
|
A -> B

But I want to do this:

A -> B -> C (using different fields in B for the B -> C join than for the A -> B join).

Of course, I can do a join, hold the results, and then join again, but it appears that I cannot do the three-table join all in one step. I can build it in the join editor, but I always get the error "(FOC003) THE FIELDNAME IS NOT RECOGNIZED:".

I believe that if the fields were all indexed, I could hide joins in the master files and accomplish the intended result that way, but simply creating the joins in my report always fails.

Another way to ask the question might be. . . Is there any type of join structure that allows multiple host files in the join structure?

Could someone confirm whether my thinking is or is not correct?

Thank you!
Rob

This message has been edited. Last edited by: MathematicalRob,

What type of data? This is a join of 3 tables in SQL Server Northwind.

JOIN
EMPLOYEES.EMPLOYEES.EMPLOYEEID IN employees TO MULTIPLE
EMPLOYEETERRITORIES.EMPLOYEETERRITORIES.EMPLOYEEID IN employeeterritories
TAG J0 AS J0
END
JOIN
J0.EMPLOYEETERRITORIES.EMPLOYEEID IN employees TO MULTIPLE
TERRITORIES.TERRITORIES.REGIONID IN territories TAG J1 AS J1
END
TABLE FILE EMPLOYEES
SUM
     J1.TERRITORIES.REGIONID
BY  EMPLOYEES.EMPLOYEES.LASTNAME
BY  EMPLOYEES.EMPLOYEES.TITLE
BY  LOWEST J0.EMPLOYEETERRITORIES.EMPLOYEEID
BY  J1.TERRITORIES.TERRITORYDESCRIPTION
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END

This message has been edited. Last edited by: BabakNYC,

Any! In my case, the first join is composed of four fields, NUMBER and VARCHAR2 (text), and the second is a single VARCHAR2. . .

Thanks for the example. . . but this example has a single host table - it's what I like to call a hub-and-spokes type join vs. a chain-type join that I'm wondering about. Am I making sense?

can you please post the code that is giving you this error ?

RSquared, Thank you - while trying to clean up my code to post, I found that I had an alias
problem, and then was able to get my code to work. I'm sorry, I can't figure out how to
demo this with the IBI sample files, so here's my own code.

The answer? I was wrong, WebFocus can do the join. Here's evidence:

JOBHIST - Weekly work history
STUJOBS - Job Assignments
JOBPLAC - Workplace Description
STUINFO - Employee Name

Note that I could have accomplished this particular report without addressing the question by using
STUJOBS as my single HOST file, but the question stands on its own. . .

SET ALL=OFF
JOIN INNER JOBHIST_PIDM IN JOBHIST TO MULTIPLE
           STUINFO_PIDM IN STUINFO AS J1
 END
JOIN INNER JOBHIST_AIDY_CODE AND JOBHIST_PIDM AND JOBHIST_POSN AND JOBHIST_SUFF IN JOBHIST TO MULTIPLE 
           STUJOBS_AIDY_CODE AND STUJOBS_PIDM AND STUJOBS_POSN AND STUJOBS_SUFF IN STUJOBS AS J2
 END
JOIN INNER STUJOBS_PLACE_CDE IN JOBHIST TO UNIQUE
           JOBPLAC_PLACE_CDE IN JOBPLAC AS J3
 END
? JOIN
CHECK FILE JOBHIST PICTURE

TABLE FILE JOBHIST
PRINT
...

The join info produced is a follows:

 
JOINS CURRENTLY ACTIVE
HOST                           CROSSREFERENCE
FIELD        FILE     TAG      FIELD        FILE     TAG      AS       ALL WH
-----        ----     ---      -----        ----     ---      --       --- --
JOBHIST_PIDM JOBHIST           STUINFO_PIDM STUINFO           J1       Y   N
JOBHIST_AID> JOBHIST           STUJOBS_AID> STUJOBS           J2       Y   N
STUJOBS_PLA> JOBHIST           JOBPLAC_PLA> JOBPLAC           J3       N   N


1SECTION 01
STRUCTURE OF GNTINT   FILE JOBHIST  ON 04/06/16 AT 10.59.25
JOBHIST
01      S0
**************
*JOBHIST_AID>**
*JOBHIST_PIDM**
*JOBHIST_POSN**
*JOBHIST_SUFF**
*            **
***************
**************
I
+-----------------+
I                 I
I STUJOBS         I STUINFO
02    I KM        04    I KM
..............    ..............
:STUJOBS_AID>::K  :STUINFO_PIDM::K
:STUJOBS_PIDM::   :STUINFO_ID  ::
:STUJOBS_POSN::   :STUINFO_LAS>::
:STUJOBS_SUFF::   :STUINFO_FIR>::
:            ::   :            ::
:............::   :............::
.............:    .............:
JOINEDI STUJOBS  JOINED  STUINFO
I
I
I JOBPLAC
03    I KU
..............
:JOBPLAC_PLA>:K         <- Look, a third tier! A grandchild!
:JOBPLAC_PLA>:
:JOBPLAC_SUP>:
:JOBPLAC_ACT>:
:            :
:............:

Thanks again,
Rob