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     Join Issue - Holding data then joining

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Join Issue - Holding data then joining
 Login/Join
 
Suzanne
Gold member
posted
We have a fex that pulls data from sourceA, then joins to SourceB - no issues. We print the data and hold as LIST1. When we try to join List1 to another table in SourceB, the data is not returned from the SourceB table we joined to. It is a one to many relationship. I have tried left outer, set all on and joining to all. Any ideas? (We recently had an issue joining in SourceB b/c of data types being A18 in one table and A18v in another, so we put ENGINE SQLORA SET VARCHAR OFF in the configuration file - not sure if that matters.)


WF 766
Win2K
 
Posts: 50 | Location: North Carolina | Registered: May 16, 2006Report This Post
Suzanne
Gold member
posted Hide Post
The tables will join correctly if we hold SourceB (Oracle) data then Join to LIST1.

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


WF 766
Win2K
 
Posts: 50 | Location: North Carolina | Registered: May 16, 2006Report This Post
FrankDutch
Virtuoso
posted Hide Post
You should do a table hold with an index like

TABLE FILE XXX
PRINT B C D
BY A
ON TABLE HOLD AS LIST1 FORMAT FOCUS INDEX A
END

The format of the fields you want to join need to have the same format (I3 or A5 or something like that)




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
Suzanne
Gold member
posted Hide Post
I get the following error when attepting to add the index. When I defined the field to make the field name shorter, no records were returned.
NAME OF INDEX FIELD OR TEXT FIELD EXCEEDS 12 CHARACTERS: INPATIENT_DATA_ID


WF 766
Win2K
 
Posts: 50 | Location: North Carolina | Registered: May 16, 2006Report This Post
FrankDutch
Virtuoso
posted Hide Post
Sorry I forgot to mention that the index field shout be less than 12 characters.
Try a real short field name p.e. IMP_ID.




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
Suzanne
Gold member
posted Hide Post
No luck...the only way we can get the data from the Oracle table to return is to hold the data and then join to the other WF table. We really need to be able to join directly. Any other ideas? I have also opened a case with tech support.


WF 766
Win2K
 
Posts: 50 | Location: North Carolina | Registered: May 16, 2006Report This Post
linus
Platinum Member
posted Hide Post
Try holding your list1 as format alpha then use list1 as the host file.


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
jgelona
Master
posted Hide Post
I do this all the time. Here's an example:

JOIN PL_CLID IN KDSPLCMT TO CL_CLID IN KDSCLINT
-*
TABLEF FILE KDSPLCMT
SUM MAX.PL_NDATE AS PLCMT_IN
MAX.CL_BIRTH_DT
MAX.CL_FNM
MAX.CL_LNM
MAX.CL_PETHNIC
MAX.CL_GENDTYPE
COMPUTE BEGIN_DT/YYMD MISSING ON=MISSING;
BY PL_CLID
WHERE PL_VOID_SW IS MISSING
AND PL_NDATE IS-NOT MISSING
AND PL_XDATE IS MISSING
AND RS_RESOURCE IN (1838,1839,1840,1841)
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS WK613W_A FORMAT FOCUS INDEX PL_CLID
END
-*
DEFINE FILE KDSPLCMT CLEAR
END
-*
JOIN CLEAR *
-RUN
-*
-TYPE .
-TYPE . Get Removal Date
-TYPE .
-*
JOIN PL_CLID IN WK613W_A TO ALL RM_CLID IN KDSCLRMV
-*
TABLE FILE WK613W_A
SUM MAX.RM_FRDT AS BEGIN_DT
BY PL_CLID
WHERE RM_RETURN_DT IS MISSING
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD
END
-*
JOIN CLEAR *
-*
MODIFY FILE WK613W_A
FIXFORM FROM HOLD
MATCH PL_CLID
ON MATCH UPDATE BEGIN_DT
ON MATCH/NOMATCH GOTO TOP
DATA ON HOLD
END
-*
FILEDEF HOLD CLEAR
-RUN
-UNIX rm hold*
-*


All of the tables that begin with KDS are Oracle tables.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Jeeva
Member
posted Hide Post
We also have the same issue after upgrading to 7.6.2. IBI suggested re-creating metadata with format AnV. That resolved the join issue but we have some other problems, like in some DEFINES we have to use same AnV format if you use it in a join or WHERE clause.


Production WebFOCUS 7.6.2, Platform Win2003, Database Oracle 10g
 
Posts: 26 | Location: Michigan | Registered: July 13, 2005Report This Post
jgelona
Master
posted Hide Post
Jeeva,

We had the same issue when moving from 5.x to 7.x. We had been running on masters that were generated way back in FOCUS for HP-UX that did not support AnV fields. When we got to 7.x, the JOINs in a lot of programs did not work. Also, anytime we created a new master it was built with AnV fields instead of An fields. The fix was to add the following to the edasprof:

ENGINE SQLORA SET ORACHAR VAR
ENGINE SQLORA SET VARCHAR OFF

That returned WebFOCUS back to the old way of handing Oracle VarChar fields.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 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     Join Issue - Holding data then joining

Copyright © 1996-2020 Information Builders