Re: Unable to submit Oracle PL/SQL statement - Qlik Community

Anonymous · ‎2016-02-12

Hello,

I got my odbc data source setup to connect to an Oracle 11g instance. I also got the Oracle connection string

created in script editor and tested it. However, when I plugged in my Oracle PL/SQL statement, I got an "Unknow Statement"

error. I have been testing this statement in Oracle SQL Developer and it's running fine.

Can someone provide some help to me as to what I need to do in order to submit this query to Oracle and

get the data back?

Thank you.

Anonymous · ‎2016-02-12

Just wanted to post additional notes. I'm using the WITH CLAUSE and a SELECT statement similar to the example

found in this link: https://oracle-base.com/articles/misc/with-clause

Is this an issue (or limitation) in Qlikview?

Anonymous · ‎2016-02-12

Could you share the script ?

Anonymous · ‎2016-02-12

Bill,

Unfortunately I won't be able to post the entire statement here due to sensitive info. But here's a simplified version of it which looks like this:

WITH

BALANCE AS

(

SELECT col1

FROM MyTable

WHERE col1='abc'

GROUP BY col1

ORDER BY col1

)

SELECT * FROM BALANCE

As soon as I press CTRL+R, I get "Unknown Statement". However if I replaced the above with just this:

SELECT col1

FROM MyTable

WHERE col1='abc'

GROUP BY col1

ORDER BY col1

It works. So it appears I can only place SELECT statements in the script editor. But I can't confirm this as I'm new to QV. So I will need the QV experts to help me here.

Thank you.

sathishkumar_go · ‎2016-02-12

Please share the error message and your script

-Sathish

Clever_Anjos · ‎2016-02-12

Please try

SQL

WITH

BALANCE AS

(

SELECT col1

FROM MyTable

WHERE col1='abc'

GROUP BY col1

)

SELECT col1 FROM BALANCE ORDER BY col1

maxgro · ‎2016-02-12

maybe you need a sql

SQL

WITH

BALANCE AS

(

SELECT col1

FROM MyTable

WHERE col1='abc'

GROUP BY col1

ORDER BY col1

)

SELECT * FROM BALANCE;

Anonymous · ‎2016-02-12

I'm glad I was able to locate a solution found in this link:

community.qlik.com/message/973758#973758

It was as simple as adding the prefix "SQL" to my entire statement. It's working now.

Clever_Anjos · ‎2016-02-12

Or even without ORDER BY (this clause does not make sense when loading data inside QV)

Anonymous · ‎2016-02-12

Yes, that's correct. I found the solution with the link I posted as well.

Thank you.