Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
ashokpaladugula
Contributor III
Contributor III

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

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

2 Replies
Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein