Our Oracle database is configure to use UTF8. So we have to set the NLS in webfocus to UTF8 to match the database. Otherwise alpha fields of the webfocus master files will have 3 times the actual size of that in the database.

But then we found another problem. With the Webfocus NLS set to use UTF8, all the joins between a hold file, either in in foc or ftm formats, returns no result.

Here is some simple code I ran off the Oracle db. I generated an hold file from PS_NAMES table, then join it back to the table. When the NLS code page is "US 437", there are 2 matches which is what I expected. When NLS code page is UTF8 there is no match

SET HOLDLIST=PRINTONLY
SET ASNAMES=ON

TABLE FILE PS_NAMES
PRINT 
FIRST_NAME AS F_NAME
LAST_NAME AS L_NAME
BY EMPLID
WHERE EMPLID IN ('220131256', '220105095')
ON TABLE HOLD AS HOLD1 FORMAT FOCUS INDEX EMPLID
END
-RUN

JOIN CLEAR *
JOIN INNER EMPLID IN HOLD1 TO ALL EMPLID IN PS_NAMES

TABLE FILE HOLD1
PRINT 
EMPLID
F_NAME
FIRST_NAME
L_NAME
LAST_NAME
END

This message has been edited. Last edited by: <Kathryn Henning>,

Found a similar case here:


https://techsupport.informatio...om/sps/60112527.html


But "Use SQL SQLORA SET ORACHAR FIX comparisons between An fields in the Master File Description and CHAR datatypes in Oracle." still can't fix my problem...

Why are you doing this?
First you create a hold file from your original database that holds a few lines...should be possible 2.

Then you join that focus file with the original oracle db to get the same result...

Did you add a debugging echo?
If so...
What does the logging say..

If not, start with that...

quote:

Originally posted by FrankDutch:
Why are you doing this?
First you create a hold file from your original database that holds a few lines...should be possible 2.

Then you join that focus file with the original oracle db to get the same result...

Did you add a debugging echo?
If so...
What does the logging say..

If not, start with that...

This is just a demo to show that join fields are from the same data.

Here is the debug log. Note that if the join direction is reversed it has matches.

 SET HOLDLIST=PRINTONLY
 SET ASNAMES=ON
 SET LINES=9999
 
 TABLE FILE PS_NAMES
 PRINT
 FIRST_NAME AS F_NAME
 LAST_NAME AS L_NAME
 BY EMPLID
 WHERE EMPLID IN ('220131256', '220105095')
 ON TABLE HOLD AS HOLD1 FORMAT FOCUS INDEX EMPLID
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=        2  LINES=      2
 -*------------------------------------------------------
 JOIN CLEAR *
 JOIN INNER EMPLID IN HOLD1 TO ALL EMPLID IN PS_NAMES
 TABLE FILE HOLD1
 PRINT
 EMPLID
 F_NAME
 FIRST_NAME
 L_NAME
 
 LAST_NAME
 ON TABLE HOLD AS HOLD_A
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=        0  LINES=      0
 -*------------------------------------------------------
 JOIN CLEAR *
 JOIN INNER EMPLID IN PS_NAMES TO ALL EMPLID IN HOLD1
 TABLE FILE PS_NAMES
 PRINT
 EMPLID
 F_NAME
 FIRST_NAME
 L_NAME
 LAST_NAME
 ON TABLE HOLD AS HOLD_B
 END
 -EXIT
 0 NUMBER OF RECORDS IN TABLE=        2  LINES=      2

Hi,
Just something to perhaps check.
One of our customers recently experienced "funnies" with their JOINs in SQL Server, and the issue turned out to be related to JOINing on varchar columns.
Perhaps that could also be a factor in your example.

What is the data type EMPLID in the master file of PS_NAMES?
What is the data type EMPLID in the hold file HOLD1?
Are these two the same?

Also you could try the conditional join syntax.

  
JOIN CLEAR *
JOIN INNER
   FILE HOLD1     AT EMPLID TAG T1 TO ALL
   FILE PS_NAMES  AT EMPLID TAG T2 AS JA
   WHERE T1.EMPLID = T2.EMPLID;
END

This has been a long going case and finally we found the cause and a solution with help from Customer Support.

Twanette was right. The root cause was NVARCHAR2 in Oracle, as well as the UTF code page. Here we actually had 2 problem. One is as described above. The data file was generated from the Oracle database. This one is fixed by removing the

SQLORA VARCHAR OFF

from the server setting file.

Then we still had another issue, when the source is a flat file or Excel file, they still do not join even the join values are the same. After a few more days of investigation, IBI found a solution that we had to use a TRIMV function to remove all possible empty spaces of input file join column, even though there is no trailing spaces in the input file, and the data type is set to the same AnV of the database file.