topic AW:SQL error: ORA-01858 when loading View in QlikView

SQL error: ORA-01858 when loading View

Fri, 29 Apr 2011 18:56:13 GMT

I have added a custom view to my script this morning and upon reload, I am receiving the following error message:

SQL error: ORA-01858: a non-numeric character was found where a numeric was expected

Script line: SELECT * FROM SYSADM."PS_W_CRSE_HRS_VW"

SQL SELECT *

FROM SYSADM."PS_W_CRSE_HRS_VW"

The Script is as follows:

LOAD

"LM_CI_ID",

"LM_DMTHD_ID",

"LM_DURN_HRS";

SQL SELECT *

FROM SYSADM.PS_W_CRSE_HRS_VW;

The view contains 3 fields, all of which are numeric (no dates). I have tried using SELECT on each individual field and receive the message on each reload, no matter what field is selected. The view runs fine with a SELECT * FROM SYSADM.PS_W_CRSE_HRS_VW in SQL Plus and SQL developer, so my PeopleSoft team is telling me this is an error on the QlikView side. Any tips/advice? I don't think this is a QV issue, but they insist it is.

SQL error: ORA-01858 when loading View

Anonymous — Fri, 29 Apr 2011 19:10:27 GMT

QlikView would not generate an ORA- error. This error is generated by Oracle even before QlikView had a chance to receive the data and execute the LOAD statement.

Would it be possible to review the SQL behind the VIEW and see if there is any formatting code in the VIEW? Would it also be possible to go directly against the base table instead of the VIEW?

Regards,

Dinesh.

AW:SQL error: ORA-01858 when loading View

Fri, 29 Apr 2011 21:53:49 GMT

Hello Jennifer,

as Dinesh mentioned above:


Jennifer Rodriguez wrote:SQL error: ORA-01858: a non-numeric character was foundwhere a numeric was expected . . .
I don't think this is a QV issue, but they insist it is.

This is obviously and definitely an oracle error! Tell this your oracle-"experts" at home.


Jennifer Rodriguez wrote:The view runs fine with a SELECT * FROM SYSADM.PS_W_CRSE_HRS_VW in SQL Plus and SQL developer,

It seems that SQL*Plus and SQL-Developer run in another oracle-enviroment than Qlikview-ODBC does. I suspect that the Oracle NLS-Parameters of these are different. So check this out or let check this out. NLS means National Language Support and includes a lot of default formats like for currency and dates(!!). These default formats are used in SQL when formating numbers, dates, currencies. And they can be changed in at least three ways: for the whole database, for one instance of a database, or for one particular session (connect).

To help You to help your oracle guys, play with the following code (may be, you do not have all rights to access all tables, but it is worth a try):

ODBC CONNECT TO dwh_lokal (XUserId is KUXYISdOIbXYWXNNBDbSC, XPassword is aQONBSdOILLA);

// some testing, try to produce some errors
SELECT to_date('21-JAN-2011', 'DD-MM-YYYY') AS myVal
from dual;
SELECT to_number('abc') AS myVal
from dual;
SELECT to_number('123') AS myVal
from dual; // at least this should work

//what are your personal date-formats in Oracle(!) ?
SELECT * FROM nls_session_parameters WHERE parameter Like '%DATE%';
SELECT * FROM nls_instance_parameters WHERE parameter Like '%DATE%';
SELECT * FROM nls_database_parameters WHERE parameter Like '%DATE%';

See what parameters you recieved in nls_session_parameters and what is in dual.myVal.

HTH, Roland

AW:SQL error: ORA-01858 when loading View

Thu, 10 Nov 2011 16:38:33 GMT

Jennifer,

Did you ever figure this one out? I too am having the same issue with this load from Oracle. ErrorSources: OraOLEDB, ErrorMsg: ORA-01858: a non-numeric character was found where a numeric was expected.

Any help would be appreciated! PLEASE!