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     Value for Join 'FROM' Field Out of Sequence

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Value for Join 'FROM' Field Out of Sequence
 Login/Join
 
Member
posted
Hello,

I am pretty new to Webfocus and I am hoping someone here can help me with the problem I am having with couple of reports. It seams that when we updated to 7.6.2 that webfocus got more sensitive. Anyways, when I am trying to join to hold files I am getting the above error message (FOC1070). Obviously, there is more to the code, but I am unsure if even this help in determening the right solution for this.

[SET ASNAMES = ON
TABLE FILE EVENT
SUM
STECUC
STEHBL
STECT#
LST.STEPNM AS 'STEPNM'
LST.STETXT AS 'STETXT'
LST.STESQD AS 'STESQD'
LST.STESQT AS 'STESQT'
BY STECUC NOPRINT
BY STEHBL NOPRINT
BY STECT# NOPRINT
ON TABLE HOLD AS RAIL_EVENTS
-*ON TABLE PCHOLD FORMAT EXL2K
END
-*-EXIT
-**********************************************************************************
JOIN CLEAR *

SET ALL=ON

JOIN CNEE AND HBL AND CNTR# IN ACTIVITY
TO ALL STECUC AND STEHBL AND STECT# IN RAIL_EVENTS AS J1
END

Thanks in advance,

Natalija


WF- 7.6.2 OS-Windows XP Output - EXL2K
 
Posts: 4 | Registered: February 01, 2008Report This Post
Virtuoso
posted Hide Post
quote:
STECUC
STEHBL
STECT#
LST.STEPNM AS 'STEPNM'
LST.STETXT AS 'STETXT'
LST.STESQD AS 'STESQD'
LST.STESQT AS 'STESQT'
BY STECUC NOPRINT
BY STEHBL NOPRINT
BY STECT# NOPRINT


Without seeing the original file, try taking the noprint off the BY statements since you are using SUM you don't need to restate the items if you are doing a holdfile.

Also, are there multiple records in ACTIVITY for each or is it a one to one relationship?


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Virtuoso
posted Hide Post
Natalja

If you want to join two tables a simple "hold" would not work.
Change it to

ON TABLE HOLD AS RAIL_EVENTS FORMAT FOCUS INDEX STECUC STEHBL STECT#


Or an other format that is the same as the format of your host table.

and please update your signature to let us know what version you use.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
Actually, joining two hold files in fixed format does work although not very efficiently. The target file MUST be sorted in the same manner as the host file.

Frank's way is recommended for the target. The host file can remained fixed format.


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
Member
posted Hide Post
Thank you all for your responses.
I have tried FORMAT FOCUS INDEX STECUC STEHBL STECT#, but i got antoher error message (FOC236) Linked file does not have matching key field or sagment.
I am joining customer to customer, bill of lading to bill of lading and container# to container#. Now, there are multiple events for each record in ACTIVITY hold file therefore in RAIL_EVENTS hold file I pick only the last event to match up to recod in ACTIVITY hold file. Do the field names in FORMAT FOCUS INDEX FIELD FILED FIELD have to have the exact same name? Or just the key fields have to match?

Again, thank you all for you help!


WF- 7.6.2 OS-Windows XP Output - EXL2K
 
Posts: 4 | Registered: February 01, 2008Report This Post
Virtuoso
posted Hide Post
If you want only last RAIL_EVENTS, have you considered reversing the join and table file? You've not mentioned how the ACTIVITY file is created, does it have multiple entries and sorted in the same order? Do you want all activity on one rail event?


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Expert
posted Hide Post
Viva, the way i read your code, your designated key fields are STECUC, STEHBL, AND STECT#.
You need to sort your output file BY those fields so that focus knows how your extract is ordered, and your fex is using NOPRINT which means your extract file doesn't actually contain any keys, which explains the FOC236 error
SUM
LST.STEPNM AS 'STEPNM'
LST.STETXT AS 'STETXT'
LST.STESQD AS 'STESQD'
LST.STESQT AS 'STESQT'
BY STECUC
BY STEHBL
BY STECT#
ought to do it for you.

.. of course if in fact STECUC et.al. need to be summed to create the key field values, then do so in a TABLE FILE above this one, a 2step process.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Virtuoso
posted Hide Post
I would also recommend to shorten the name of the hold file.
I try to make hold files always as an 8 character file name.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
<Shyam L>
posted
Natalija,
You need not necessarily have to index your hold files for achieving the same. Ensure that all your files are ordered by the key fields with which you are joining. I encountered the same issue and was able to solve it.
 
Report This Post
Platinum Member
posted Hide Post
In your cross referenced FOCUS file you must concatenate the three fields into one to use them in a join. That's what your new error is telling you.


WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
 
Posts: 149 | Location: Dallas, TX | Registered: June 08, 2007Report This Post
Expert
posted Hide Post
quote:
You need not necessarily have to index your hold files for achieving the same. Ensure that all your files are ordered by the key fields with which you are joining. I encountered the same issue and was able to solve it.


Which is exactly what I told her to do on the 25th.


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
Master
posted Hide Post
You should be able to join on 3 fields, but the format of all 3 have to be identical in both files. I would also rename the fields with # in the name, special character other than underscore can sometimes cause a problem especially in an Index. What is the format of the ACTIVITY file, is it a flat file or a database file? Where are the join fields in the Activity file (are they the first 3 fields, are they in the same segment)? Concatenating the 3 fields in both files and sorting on the concatenated field may be a good suggestion. The fields all need to be alphanumeric or converted to alphanumeric to do this. I would recommend sorting your Activity file BY CNEE BY HBL BY CNTR (DEFINE the name to remove the #).


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Platinum Member
posted Hide Post
Page 662 of Creating Reports WF7.1.3 manual clearly states that when joining two FOCUS files you may not specify more than one field in the cross referenced file -- therefore if you need to join on more than one field in the cross referenced file you must concatenate them together.


WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
 
Posts: 149 | Location: Dallas, TX | Registered: June 08, 2007Report This Post
Gold member
posted Hide Post
As linus stated, when joining to a FOCUS file, you can only join to a single field within the file.

Use one of the following options
1. Join to all STECUC and use a where statement to match on the other fields.
2. Create a single field with all of the values concatinated to build a key and use the key for the join (as stated above - note: this will be faster, only if the guest file contains many more records than what you will actually be joining to.)
3. Follow Susanah's advice on building the correct sort order. (note: this will most likely be most efficient since you will not have to spend the extra processing of building a FOCUS file with indexes)



Windows: WF 7.6.2: SQL Server 2008 R2
 
Posts: 86 | Location: Chicago | Registered: August 03, 2007Report This Post
Virtuoso
posted Hide Post
Two suggestions, both of which can work for Focus files:

1. "conditonal join" (see syntax at
documentation.informationbuilders.com/masterindex/html/html_wf_761/wf761crlang/source/topic141.htm#djoin1010390) -- join on one of the fields, specify additional equality constraints in WHERE.

2. MDI (multi-demensional index)


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report 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     Value for Join 'FROM' Field Out of Sequence

Copyright © 1996-2020 Information Builders