topic Re: Loading a SQL API (function) field not found error in QlikView

Loading a SQL API (function) field not found error

matthewp — Thu, 01 Sep 2016 13:07:23 GMT

I am getting a field not found <TTT_NO> error when trying to runt his load script:

super:

LOAD "SSS_NO" AS [S Number],

"TTT_NO" AS [T Hours]; ----- THIS LINE

SQL SELECT

et.SSS_NO,

et.CCC_ID,

WSA_API.GSH (et.CCC_ID, et.SSS_NO, trunc(sysdate)+2) AS TTT_NO

FROM SSS_TABBLE et;

STORE super INTO $(vDetailsQVDDirectory)super.qvd (qvd);

DROP TABLE super;

Re: Loading a SQL API (function) field not found error

Anil_Babu_Samineni — Thu, 01 Sep 2016 13:23:52 GMT

Because you are attempting the preceding load. Can I know what are you expecting from this. And please try database and then try in qlikview

Re: Loading a SQL API (function) field not found error

matthewp — Thu, 01 Sep 2016 13:24:40 GMT

SELECT

et.SSS_NO,

et.CCC_ID,

WSA_API.GSH (et.CCC_ID, et.SSS_NO, trunc(sysdate)+2) AS TTT_NO

FROM SSS_TABBLE et;

works fine in oracle sql developer

Re: Loading a SQL API (function) field not found error

Anil_Babu_Samineni — Thu, 01 Sep 2016 13:27:03 GMT

Then use same expression to get the values using alias names then trace to debug see whether the values are coming or not

Re: Loading a SQL API (function) field not found error

matthewp — Thu, 01 Sep 2016 13:27:53 GMT

I want to run the sql in the load script not in the qvd extract

Re: Loading a SQL API (function) field not found error

trdandamudi — Thu, 01 Sep 2016 13:33:26 GMT

Give a try on the below code and let me know the result:

super:

LOAD *;

SQL SELECT

et.SSS_NO,

et.CCC_ID,

WSA_API.GSH (et.CCC_ID, et.SSS_NO, trunc(sysdate)+2)

FROM SSS_TABBLE et;

STORE super INTO $(vDetailsQVDDirectory)super.qvd (qvd);

DROP TABLE super;

Re: Loading a SQL API (function) field not found error

stabben23 — Thu, 01 Sep 2016 13:35:39 GMT

trunc(sysdate) is not valid in SQL use something like CONVERT(date,getdate()) instead

Re: Loading a SQL API (function) field not found error

trdandamudi — Thu, 01 Sep 2016 13:37:47 GMT

Trunc() is a valid function in Oracle unless there is a change, I used to use Oracle long years back.

Re: Loading a SQL API (function) field not found error

matthewp — Thu, 01 Sep 2016 13:38:59 GMT

The SQL is fine, its the QV part

Re: Loading a SQL API (function) field not found error

Peter_Cammaert — Thu, 01 Sep 2016 13:43:57 GMT

I would rather test it like this:

super:

SQL SELECT

et.SSS_NO,

et.CCC_ID,

WSA_API.GSH (et.CCC_ID, et.SSS_NO, trunc(sysdate)+2) AS TTT_NO

FROM SSS_TABBLE et; // Correct spelling?

Now check in the Table Viewer which fields names you do get in the super table...

Peter