[CLOSED] webfocus hold file join to unicode database returns no match
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>,
7.66 and 7.704 System: Windows / AIX / Linux Output: Mostly HTML, with some PDF, Excel and Lotus(!)
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...
7.66 and 7.704 System: Windows / AIX / Linux Output: Mostly HTML, with some PDF, Excel and Lotus(!)
April 23, 2013, 06:52 PM
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...
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
April 24, 2013, 09:56 AM
bug
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
7.66 and 7.704 System: Windows / AIX / Linux Output: Mostly HTML, with some PDF, Excel and Lotus(!)
April 24, 2013, 05:45 PM
Twanette
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.
WebFOCUS 8.2.06 mostly Windows Server
April 25, 2013, 09:11 AM
Mighty Max
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.
7.66 and 7.704 System: Windows / AIX / Linux Output: Mostly HTML, with some PDF, Excel and Lotus(!)