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     Multi Field to Single field JOIN

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Multi Field to Single field JOIN
 Login/Join
 
Rayden
Member
posted
Hi All,

I have a file keyed as follows:

fldA, 2A
fldB, 2A
fldC, 2A
fldD, 2,0 numeric
fldE, 6,0 numeric

and a second file keyed by one 17A field where

fldA + fldB + fldC + fldD + fldE + 3spaces = the 17A key field.

What syntax do I use on my JOIN statement to link the two differing types of file key structures?

Thanks,
Rayden.


Web Focus version 7.1.4
Server Windows Server 2003
 
Posts: 26 | Registered: May 24, 2006Report This Post
smiths
Platinum Member
posted Hide Post
DEFINE FILE YOURFILE
-* To pad with leading zeroes...
a_fldD/A2 = EDIT(fldD);

-* To not pad...
a2_fldD/A2 = FTOA(fldD, '(Dc2)', a2_fldD);

-* Same idea with fldE

combined/A17 = fldA | fldB | fldC | a_fldD | a_fldE;

END

Now you should be able to join on the field called combined.

Sean


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report This Post
susannah
Expert
posted Hide Post
we don't know what kind of file you have?
is it focus?
is it a rdb?
fields with embedded blanks can be tricky



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
Rayden
Member
posted Hide Post
Thanks for the inpust so far.

To give you more detail both files are DB2 tables.

I have tried defining an A17 field concatenanted from the multiple fields in file1 but when I try to join on it to the other file I get this error:

(FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE: A_SID#
BYPASSING TO END OF COMMAND

A_SID# is the new field I defined in file1.

What have I missed?

Thanks,
Rayden.


Web Focus version 7.1.4
Server Windows Server 2003
 
Posts: 26 | Registered: May 24, 2006Report This Post
smiths
Platinum Member
posted Hide Post
The error message refers to file A_SID#, however you refer to field name A_SID#. Something isn't adding up. Can you post the JOIN statement that you are using, plus some of the relevant code leading up to that join?

Sean


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report This Post
Rayden
Member
posted Hide Post
Hi again,

Here is the define:

DEFINE FILE xxx
A_4/A2=EDIT(xxfl4,'99');
A_5/A6=EDIT(xxfl5,'999999');
A_6/A3=EDIT(xxfl6,'999');

A_SID#/A17=xxfl1|xxfl2|xxfl3|A_4|A_5|A_6;

SET ALL=ON
JOIN CLEAR *
JOIN xxx.A_SID# IN xxx TO ALL yyy.fl17 IN yyy AS J0
END

When I got the last message I had the join like this:

SET ALL=ON
JOIN CLEAR *
JOIN A_SID# IN xxx TO ALL yyy.fl17 IN yyy AS J0
END

Since qualifying the field with the file name I re-ran and got this error:

(FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE:
xxx.A_SID#
BYPASSING TO END OF COMMAND


I must be doing something dumb, but I have not had a lot of experience with this product.

Really appreciate your time and assistance.

Rayden.


Web Focus version 7.1.4
Server Windows Server 2003
 
Posts: 26 | Registered: May 24, 2006Report This Post
smiths
Platinum Member
posted Hide Post
Rayden,

Sorry, I misinterpreted the error message previously, but....

You need to close off your DEFINE FILE xxx with an END statement. Do you have the END statement in your source code?

Sean


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report This Post
Rayden
Member
posted Hide Post
Hi Sean,

I do have an END prior to the JOIN, there is some other define code also but not pertaining to the fields for the key to file2, so I omitted it in my POST.

As the xxx file is a DB2 table pulled into the metadata, does that limit the define capbilities, and only lets me use actual fields from xxx in the JOIN statement?

Thanks,

Rayden,


Web Focus version 7.1.4
Server Windows Server 2003
 
Posts: 26 | Registered: May 24, 2006Report This Post
smiths
Platinum Member
posted Hide Post
Rayden,

Hmmm. I don't use DB2, so I don't know if that's an issue or not. Anybody else know?

I'll try to dig after the Web conference if nobody finds anything.

Sean


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report This Post
j.gross
Virtuoso
posted Hide Post
quote:
JOIN xxx.A_SID# IN xxx TO ALL yyy.fl17 IN yyy AS J0


When joining on a defined field, you need to specify WITH . That will signal that it's a defined field (in fact a yet-to-be-defined field), and also that the retrieval via the join must be refreshed whenever a new instance of the segment containing the real field is read.

JOIN xxx.A_SID# WITH xxfl6 IN xxx TO ALL yyy.fl17 IN yyy AS J0
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Leah
Virtuoso
posted Hide Post
quote:
JOIN xxx.A_SID# IN xxx TO ALL yyy.fl17 IN yyy AS J0

I do believe you need to add a WITH in your join. JOIN xxx.A_SID# WITH FLDA IN xxx TO ALL yyy.fl17 IN yyy AS J0
I've done it against DB2 using a defined field in the join.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Rayden
Member
posted Hide Post
Hi All,

I got it. I was doing 2 things wrong. First the JOIN has to be issued before the DEFINE, and secondly as j.g. stated I needed the WITH to let the JOIN know the field is to be defined.

Thanks to all for your input.

Back to work..

Rayden.


Web Focus version 7.1.4
Server Windows Server 2003
 
Posts: 26 | Registered: May 24, 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     Multi Field to Single field JOIN

Copyright © 1996-2020 Information Builders