topic Re: With CTE SQl query resulting error on schedule reload. mannually its running in QlikView

With CTE SQl query resulting error on schedule reload. mannually its running

SunilChauhan — Mon, 29 Dec 2014 04:39:56 GMT

Hi all,

With CTE SQl query resulting error on schedule reload. mannually its running

SQL WITH CTE(x,y,z,) AS

( SELECT *

From table

Inner Join ...........................................

Left Outer Join .........................

Left Outer Join ...........................)

SELECT * FROM CTE WHERE ..................................);

this query running mannually. but on schedule realod its saying below erro

Error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near the keyword 'WITH'.

29/12/2014 04:30:55: General Script Error

29/12/2014 04:30:55: Execution Failed

29/12/2014 04:30:55: Execution finished.

Re: With CTE SQl query resulting error on schedule reload. mannually its running

Mon, 29 Dec 2014 04:56:05 GMT

Try to place a semicolon ; just before the WITH

SQL ;WITH CTE(x,y,z,) AS

( SELECT *

From table

Inner Join ...........................................

Left Outer Join .........................

Left Outer Join ...........................)

SELECT * FROM CTE WHERE ..................................);

The semicolon is needed for scenarios where WITH could be considered part of the previous query

Re: With CTE SQl query resulting error on schedule reload. mannually its running

SunilChauhan — Mon, 29 Dec 2014 04:59:56 GMT

not working

Re: With CTE SQl query resulting error on schedule reload. mannually its running

Mon, 29 Dec 2014 05:01:28 GMT

As WITH CTE is native to MSSqlServer, I am not sure Qview supports it 100%. For instance the following:

WITH backup_details(name,backup_type) AS

(

SELECT name,backup_type from dbo.Backup_History WHERE Server='MYTESTSERVER'

)

SELECT * from backup_details where backup_type='Database'

should be replaced in Qlikview as follows:

SQL SELECT name,backup_type from dbo.Backup_History WHERE Sever="MYTESTSERVER'

AND backup_type = 'Database'

CTE are just like temporary (more cmplex tought when they are recursive ones) Views where you can simply your select from. Otherwise you can go and create a VIEW as CREATE VIEW backup_details as SELECT name,backup_type from dbo.Backup_History WHERE Server='MYTESTSERVER' and then select from View in QlikView.

Re: With CTE SQl query resulting error on schedule reload. mannually its running

SunilChauhan — Mon, 29 Dec 2014 05:03:48 GMT

i had this idea in mind . but i thought to run thi without disturbing DBA

Re: With CTE SQl query resulting error on schedule reload. mannually its running

Mon, 29 Dec 2014 05:18:03 GMT

Hi Sunil,

I had a similar issue but found using stored procedures a good way to get around this.

Just move the logic in your SQL to a stored procedure and run as:

SQL

exec <<enter stored procedure name here>>;

I have tries using the following and it did not error:

SQL

with Data

(

select * from dbo.Dim_Store

)

select

a.*

from

Data a;

Only difference i can see is i did not define the field names in the first part of the CTE.

Cheers!

Spud

Re: With CTE SQl query resulting error on schedule reload. mannually its running

Mon, 29 Dec 2014 05:22:03 GMT

What if you put the CTE and that load in a Macro invoked from the Load script as follows;

SunilCTE;

then SunilCTE sub contains the CTE part as a native sql query as follows:

Sub SunilCTE

Set objADO = CreateObject(“ADODB.Connection”)

‘Establish a connection

objADO.Open “Provider=put your provider her etcetc;Data Source=yourDB;User ID=youruseretcetc;Password=Passkey;”

strSQL=”WITH CTE(x,y,z,) AS

( SELECT *

From table

Inner Join ...........................................

Left Outer Join .........................

Left Outer Join ...........................)"

objADO.Execute strSQL

//regular QlikView load sintax here from now on

SQL SELECT * FROM CTE WHERE ..................................);

end Sub

Re: With CTE SQl query resulting error on schedule reload. mannually its running

SunilChauhan — Mon, 29 Dec 2014 05:33:30 GMT

thanks Mario. i think its really good way to do this.

But My bad we are not using macro anywhere in applications.

could you please provide how to execute this query itself.

i know i will have to go finally for stored procedure view or select query. but i am looking there should be a way to excute this CTE query itself.

Re: With CTE SQl query resulting error on schedule reload. mannually its running

SunilChauhan — Mon, 29 Dec 2014 09:54:54 GMT

any solution for this ?