Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [WORKAROUND] (FOC370) THE FIELDNAME..NOT BE FOUND IN THE FILE, but it's right there

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[WORKAROUND] (FOC370) THE FIELDNAME..NOT BE FOUND IN THE FILE, but it's right there
 Login/Join
 
Virtuoso
posted
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,


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Platinum Member
posted Hide Post
Shouldn't the join be

JOIN STATUS IN HSAMPLES TO STATUS IN STATUS AS J1


WF 7.6.11
Output: HTML, PDF, Excel
 
Posts: 123 | Location: UK | Registered: October 09, 2003Report This Post
Virtuoso
posted Hide Post
True, and it was like that in my original query too. I'll fix the example.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
The where based JOIN is only supported as a FOCUS-to-FOCUS or non-FOCUS-to-non-FOCUS data sources only.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
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,


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Master
posted Hide Post
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


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Virtuoso
posted Hide Post
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=


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Master
posted Hide Post
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


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Master
posted Hide Post
You might be right about WF selecting individual records ( like a cursor ) and firing multiple queries to the other table. Confused

Did you try the -RUN between the joins?

...can you match it instead of join?

Greets,
dave


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [WORKAROUND] (FOC370) THE FIELDNAME..NOT BE FOUND IN THE FILE, but it's right there

Copyright © 1996-2020 Information Builders