Focal Point
Value for Join 'FROM' Field Out of Sequence

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7161053372

February 25, 2008, 04:25 PM
VivaVox
Value for Join 'FROM' Field Out of Sequence
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
February 25, 2008, 04:48 PM
Leah
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
February 25, 2008, 05:04 PM
FrankDutch
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

February 25, 2008, 05:22 PM
GinnyJakes
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
February 26, 2008, 11:35 AM
VivaVox
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
February 26, 2008, 01:24 PM
Leah
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
February 26, 2008, 02:03 PM
susannah
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
February 26, 2008, 02:56 PM
FrankDutch
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

February 27, 2008, 05:42 AM
<Shyam L>
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.
February 27, 2008, 08:35 AM
linus
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
February 27, 2008, 09:23 AM
GinnyJakes
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
February 27, 2008, 09:43 AM
PBrightwell
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
February 28, 2008, 08:45 AM
linus
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
March 03, 2008, 03:37 PM
Gizmo
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
March 03, 2008, 05:33 PM
j.gross
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