I'm using Developer Studio version 523.
My procedure is as follows:

- Define field ROOM (made by other define fields)
- Report EMPLOYEE table with ROOM, EMPID, NAME, etc
- Hold Report as EMPROOM format FOCUS Index EMPID, ROOM
- JOIN B_ROOM table to EMPROOM table by RMID->ROOM (1-to-many)
- JOIN B_ROOM table to BLDG table by BLDGCODE->BLDGCODE (many-to-1)
- Report B_ROOM with BLDG, ROOM, EMPID, NAME, etc

Now... This works as long as my hold file exists. How does the hold file not exist you ask? WellIf i first do the join with EMPROOM as the host file and do the second report on EMPROOM and attempt a run, this opens us the hold file. However, I can't get any child data, becaus ethis represents a many-to-one-join. At this point I can then, in Report painter, go to prefixes and change my join to the one described earlier. Then go to the Source field and change the table to the B_ROOM table and run the report. Like magic, everything works beautifully!

The problem? As soon as I close the report or make any changes to the hold file, my join and my report can no longer identify the EMPROOM master file. I'm told by programmers in older versions that have no GUI that they way I want to do it is possible. However, in the version, it won't let me.

HELLLLLPPPP!!!!!

To Answer your question NO a hold file does not have to be the host file of a join. One thing that you want to make sure is that whenever possible ALWAYS pass the join off the the RDBMS. This way WebFOCUS/FOCUS only has to process the a smaller result set. If you are using an RDBMS try joining the table together then create you define field.

If you are joinning two hold files together I believe one of the hold files needs to have an indexed field which has to be used in the join statement and the hold file has to be of focus format.

Your code should have something like this in it:
ON TABLE HOLD AS hold_filename FORMAT FOCUS INDEX fieldname

To be more specific, the guest file of the two in the join must be indexed on the field you are joining on..

Ex.:
JOIN FIELD IN HOSTFILE TO FIELD IN GUESTFILE AS J1

FIELD must be indexed or in sort order in GUESTFILE for your results to be accurate.