I'm trying to JOIN a FOCUS file to a set of database tables and for some reason WebFOCUS can't find the field that those database tables are supposed to be joined on, while it's right there! What's going on here?

This fails with:
(FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE: products.STATUS

TABLE FILE SAMPLES
PRINT PART_NO PRODUCTIION_DATE
WHERE SAMPLE_CODE EQ '&SAMPLE_CODE';
ON TABLE HOLD AS HSAMPLES FORMAT FOCUS INDEX PART_NO
END
-RUN

JOIN LEFT_OUTER
 FILE HSAMPLES AT PART_NO TAG samples TO MULTIPLE
 FILE PRODUCT AT PART_NO TAG products AS J0

 WHERE samples.PART_NO EQ products.PART_NO;
 WHERE samples.PRODUCTION_DATE GE products.EFFECTIVE_DATE;
 WHERE samples.PRODUCTION_DATE LE products.OBSOLESCENCE_DATE OR products.OBSOLESCENCE_DATE IS MISSING;
END
-*?FF HSAMPLES
-*-EXIT
JOIN products.STATUS IN HSAMPLES TO STATUS IN STATUS AS J1
TABLE FILE HSAMPLES
PRINT
  samples.PRODUCTION_DATE
  products.REVISION products.EFFECTIVE_DATE products.OBSOLESCENCE_DATE products.STATUS
-*SUM MAX.REVISION
-*WHERE STATUS.STATUS_TYPE EQ 'CURRENT' OR 'HISTORIC';
BY PART_NO
END

The SAMPLES table is a database table in database 'A', while the PRODUCT and STATUS tables are database tables in database 'B'. The status field is USAGE P5 and ACTUAL P3 in both the PRODUCT and STATUS tables.
Because the tables are in different databases, I can't join them directly (hence the HOLD-file).

The STATUS table is part of the TABLE request because I need to filter out STATUS-types that are not relevant to my results. Without it the request could return a different revision as the end result than the one I'm interested in (normally I'd only be interested in the result of the SUM; the PRINT statement is for debugging this issue).

If I leave out the JOIN to STATUS, the data prints and the output includes status values.

If I uncomment the ?FF and EXIT, the output for the STATUS field is:
STATUS STATUS P5

What I'm confused about is that the STATUS field is right there, but for some reason FOCUS doesn't see it. Why is that? What's going on here?

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

Shouldn't the join be

JOIN STATUS IN HSAMPLES TO STATUS IN STATUS AS J1

True, and it was like that in my original query too. I'll fix the example.

The where based JOIN is only supported as a FOCUS-to-FOCUS or non-FOCUS-to-non-FOCUS data sources only.

Really? As just the first JOIN works fine and that's FOCUS-to-non-FOCUS. The problem doesn't occur until a second non-FOCUS JOIN is added.

Anyway, if it's not supported that answers my question. It's a bit of a shame that's the case, as it seems quite possible to support such joins. More so than the also unsupported conditional self-joins on non-FOCUS tables (e.g. JOIN FILE FOO AT BAR TO MULTIPLE FILE FOO AT BAR AS J0).

I worked around the issue by defining a VIEW in the database that does the JOIN.

I was going to suggest inserting
?FF HSAMPLE
-- but I see you did that (and later commented it out).

How did it list the STATUS field? That should tell you how to code the reference in the next JOIN.


-- But why "HOLD FORMAT FOCUS" at all? Make it just plain "HOLD" (producing a flat file), and take it from there.

This message has been edited. Last edited by: j.gross,

Hi wep, ;-)

I find it useful sometimes to use
TYPE sample.mas

instead of ?FF sample
( it gives a bit more info ).


Some thing I always check.
• Format of the files?
• Does it need index to join?
• Is there another file somewhere with the same name? ( path )

Hope this helps,

Greets,Dave

The output of ?FF for that specific field is:

 ?FF HSAMPLE
 FILENAME=  HSAMPLE
...
 STATUS                       STATUS                       P5

And yes, I checked that it finds the correct file (WHENCE STATUS MASTER) and you can see from my example that the field we join on (PART_NO) is indeed indexed.

As for the other comments; we use format FOCUS because we find it more predictable in behaviour. For example, without it, settings like ASNAMES or HOLDLIST are not being honoured (as I found out in a quick test just now). We use those a lot to fix field length differences between the two databases that are involved in the issue described here (and the reason for having a HOLD-file).

WRT the TYPE command, that doesn't seem to do anything here: EDA no data
It would appear that TYPE is part of Maintain, for which we don't have a license.

That said, we have a procedure that (probably) does something similar. It makes use of CHECK FILE X HOLD to create an overview of the master file. Calling it is almost as easy: EX CHECK_FILE MASTER=X
Easier still with: LET TYPE=EX CHECK_FILE MASTER=

A,

have you traced the SQL?

Since you're joining focus to database file I presume WF first queries the stuff from the database to local focus files...
...and then do the join.

What happens if the WHERE's ...like yours:

 WHERE samples.PRODUCTION_DATE GE products.EFFECTIVE_DATE;
 WHERE samples.PRODUCTION_DATE LE products.OBSOLESCENCE_DATE OR products.OBSOLESCENCE_DATE IS MISSING;

are already applied... ...and result in 0 rows?

No local focus file is created...
...and the next join fails... ( file missing ? )

...

and what about adding an extra -RUN between the joins?

Good luck

No, I hadn't traced the SQL yet - hadn't thought of that.

I did that for the join of the FOCUS table and the first database table (after all, joining the second table as well causes an error, so that's not a useful SQL trace). Oddly enough, while the HOLD-file contains 2 records, the SQL query contains:

WHERE
 (T2."PART_NO" = :0001);

Doesn't that only allow a single input value? If so, does WebFOCUS execute the query once for every record in the HOLD-file? In this case there are only 2 records and the query is quite fast enough, but I wouldn't like to see it behave this way for larger sets or more complex queries...

The result of the query is also 2 records.
That date-range is there to pick the correct revision of the product based on the sample's production date (which I just notice is missing from my example).

It is not technically possible to create a sample of a product that doesn't exist, so the date range around the production date of the sample always results in at least one product record.
Next to that, the status-field in PRODUCT has a foreign key constraint to the STATUS table, but adding that JOIN to the request is what triggers the 'FIELDNAME NOT FOUND' error.

Therefore, it is extremely unlikely that the error with the join on status is caused by a lack of data in the result. Not only does it never get that far with the table request, but there is data that satisfies the constraints in the request.

You might be right about WF selecting individual records ( like a cursor ) and firing multiple queries to the other table.

Did you try the -RUN between the joins?

...can you match it instead of join?

Greets,
dave