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

[SunilChauhan]

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.

[#2]

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

[SunilChauhan]

not working

[#4]

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.

[SunilChauhan]

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

[#6]

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

as

(

  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

[#7]

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

[SunilChauhan]

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.

[SunilChauhan]

any solution for this ?