How to use declare, set variable and with clause in qlikview scripting using sql query

[ashokpaladugula]

Hi all,

I'm using sql query to get the data from sql database to qlikview document. After set variable query is not executing.

My query as like this.

declare @clients as varchar(200);

set @clients='test123 test345 test567';

with cnees (cnor,cnee,cont,con) as (

select

distinct

ship_consignor = js.NKConsignor

,ship_consignee = js.SKConsignee

,jc_conum

,jk_uniqueref

from table1 js

where (@clients = '' or charindex(js.oh_code,@clients, 0)!=0)

)

select

container

,size

,wgt

,vol

from (

select

container

,size=min(cont_size)

,wgt=sum(wgt)

,vol=sum(vol)

from

(select

wgt=packsbycontainer.weights

,vol=packsbycontainer.volumes

,container=packsbycontainer.jc_containernum

,cont_size=packsbycontainer.rc_code

from table1 js

where (@clients = '' or charindex(localclient.oh_code,@clients, 0)!=0)

) js

group by container

)test;

Thanks,

ASHOK

[Anil_Babu_Samineni]

Have a look here - QlikView Addict: SET vs. LET

[jonathandienst]

The problem with a SQL CTE (WITH keyword) is that it needs to be preceded by a ; (semi-colon). This also serves to terminate the SQL statement, so the WITH() never get executed. Unless someone else has a direct workaround, you may need to encapsulate the CTE query (including the declares) in a view or a stored proc to run the CTE query from QV.