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

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

ashokpaladugula — Wed, 25 Nov 2020 16:16:04 GMT

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

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

Anil_Babu_Samineni — Tue, 15 Aug 2017 11:35:09 GMT

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

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

jonathandienst — Tue, 15 Aug 2017 12:18:48 GMT

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.