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.)

The tables will join correctly if we hold SourceB (Oracle) data then Join to LIST1.

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

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)

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

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.

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.

Try holding your list1 as format alpha then use list1 as the host file.

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.

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.

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.