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     [SOLVED] Join Error Message

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Join Error Message
 Login/Join
 
Mark1
Platinum Member
posted
Hi,
I've got a fex that has worked for months and stopped working yesterday. It is because of a join. I get the following error message:

(FOC236) LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT: EDFEESII
BYPASSING TO END OF COMMAND

When I try to run this:

TABLE FILE DSRINDCS1
SUM COL01S00A COL02S00A COL03S00A COL04S00A COL05S00A COL06S00A COL07S00A COL08S00A COL09S00A COL10S00A COL11S00A COL12S00A COL13S00A COL14S00A COL15S00A
COL16S00A
BY VLEVEL
BY NEWDATE
BY MONTH
ON TABLE HOLD AS DSRINDCS2 FORMAT FOCUS INDEX VLEVEL NEWDATE
END

TABLE FILE EDFEES
PRINT ECDFEE NUMED EDFEE
BY VLEVEL
BY NEWDATE
ON TABLE HOLD AS EDFEESII FORMAT FOCUS INDEX VLEVEL NEWDATE
END

JOIN
LEFT_OUTER VLEVEL AND NEWDATE IN DSRINDCS2 TO VLEVEL AND NEWDATE IN EDFEESII AS JEDFEES
END

TABLE FILE DSRINDCS2
PRINT COL01S00A COL02S00A COL03S00A COL04S00A COL05S00A COL06S00A COL07S00A COL08S00A COL09S00A COL10S00A COL11S00A COL12S00A COL13S00A COL14S00A COL15S00A
COL16S00A EDFEE MONTH
BY VLEVEL
BY NEWDATE
-*ON TABLE HOLD AS DSRINDCS3
END

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


Windows version 768
 
Posts: 215 | Registered: March 16, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
Mark,

If VLEVEL and NEWDATE are aliases, they are not carried over to the HOLD file. Use the real column name, not the alias.

To verify this, run this command: 
?FF DSRINDCS1
The first column is the real name, the second is the alias - they may be the same or different.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Mark1
Platinum Member
posted Hide Post
Everything checks out there:

FILENAME= DSRINDCS1
VLEVEL E01 A100
MONTH E02 MTRYY
NEWDATE E03 YYMD
FILENAME= DSRINDCS2
VLEVEL E01 A100
NEWDATE E02 YYMD
MONTH E03 MTRYY
FILENAME= EDFEES
VLEVEL E01 A100
NEWDATE E02 YYMD
FILENAME= EDFEESII
VLEVEL E01 A100
NEWDATE E02 YYMD


Windows version 768
 
Posts: 215 | Registered: March 16, 2006Report This Post
Mark1
Platinum Member
posted Hide Post
One more thing that I just noticed... when I do a print * on EDFEESII, a field called FOCLIST that is not in any of my hold files anywhere! Does anybody know why WF puts a field in my hold file like that?

Another thing I just noticed is that when I join on one field it works fine. When I try to join on two fields it errors out.

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


Windows version 768
 
Posts: 215 | Registered: March 16, 2006Report This Post
GinnyJakes
Expert
posted Hide Post
It's because you did a PRINT. It forces uniqueness for each row. You don't have to worry about it.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Mark1
Platinum Member
posted Hide Post
Another thing I just noticed is that when I join on one field it works fine. When I try to join on two fields it errors out.


Windows version 768
 
Posts: 215 | Registered: March 16, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
Duh! Should have realized that! Oddly enough, only one field can be joined for FOCUS DB! Multiple columns can be joined for flat files or DBMS tables.

One solution is to define a field that is a concatenation of the two indexed fields, if of course, both are alpha or integer.

Another solution is to create flat files with the data sorted in the correct order.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
Does not work:

?FF CAR

TABLE FILE CAR
SUM 
SALES
COMPUTE CC/A100 = COUNTRY | CAR;
BY COUNTRY
BY CAR
BY BODYTYPE
ON TABLE HOLD AS H001 FORMAT FOCUS INDEX COUNTRY CAR
END

TABLE FILE CAR
SUM 
FUEL_CAP
COMPUTE CC/A100 = COUNTRY | CAR;
BY COUNTRY
BY CAR
ON TABLE HOLD AS H002 FORMAT FOCUS INDEX COUNTRY CAR
END

?FF H001
?FF H002

JOIN LEFT_OUTER COUNTRY AND CAR IN H001 TO COUNTRY AND CAR IN H002 AS J1

TABLE FILE H001
PRINT 
H001.COUNTRY
H001.BODYTYPE
END

Works:
?FF CAR

TABLE FILE CAR
SUM 
SALES
COMPUTE CC/A100 = COUNTRY | CAR;
BY COUNTRY
BY CAR
BY BODYTYPE
ON TABLE HOLD AS H001 FORMAT FOCUS INDEX CC
END

TABLE FILE CAR
SUM 
FUEL_CAP
COMPUTE CC/A100 = COUNTRY | CAR;
BY COUNTRY
BY CAR
ON TABLE HOLD AS H002 FORMAT FOCUS INDEX CC
END

?FF H001
?FF H002

JOIN LEFT_OUTER CC IN H001 TO CC IN H002 AS J1

TABLE FILE H001
PRINT 
SALES
FUEL_CAP
BY COUNTRY
BY CAR
BY BODYTYPE
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
Although, this does not explain
quote:
worked for months and stopped working yesterday


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Mark1
Platinum Member
posted Hide Post
Seriously, it had been working for months and then started to error out yesterday. At least you have given me a good example from the car file to work with. I'm not understanding why when you join on two fields... CRASH! I'm going to have to rewrite a lot of programs if this continues to be the case. Mad

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


Windows version 768
 
Posts: 215 | Registered: March 16, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
Not a crash, just out of gas.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
Here's some documentation regarding JOIN:

Creating Reporting Applications With Developer Studio > Joining and Merging Data Sources > Creating Multi-Field Joins

I was slightly wrong about the FOCUS multi-field join - two individual fields can be joined from the host file, the cross-referenced file requires the one concatenated field.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Mark1
Platinum Member
posted Hide Post
I concatenated the two fields I was joining on and used that one field in my join. That seems to work. Thanks!


Windows version 768
 
Posts: 215 | Registered: March 16, 2006Report 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     [SOLVED] Join Error Message

Copyright © 1996-2020 Information Builders