topic Re: Variable in SQL in QlikView

Variable in SQL

Fri, 15 May 2015 11:22:00 GMT

Hi,

I´d like to create simething like this:

set vtable = 'TAB01'

SQL

declare @xtab varchar(500)

declare @sql varchar(max)

set @xtab= $(vtable)

Set @sql = 'SELECT * from '+@xtab

Exec(@sql);

How can I do this?

Thanks a lot

Re: Variable in SQL

Gysbert_Wassenaar — Fri, 15 May 2015 11:35:56 GMT

SET vtable = TAB01;

Table:

SQL Select * From $(vtable);

Re: Variable in SQL

buzzy996 — Fri, 15 May 2015 11:45:31 GMT

u have to use ur variable in sql with $ symbol.

Re: Variable in SQL

Fri, 15 May 2015 11:45:51 GMT

Hi,

So, what I need is to create a variable because my sql will be dynamic! No just the table I´ll change... I used this example just so simplify!

The real situation is that I need to put many 'unions' in my sql!

Re: Variable in SQL

Gysbert_Wassenaar — Fri, 15 May 2015 11:50:45 GMT

Build up the sql statement in a variable then execute it:

SET vTable = MyTable;

LET vWhere = 'MyField > 0'

SET vSQL = SELECT * FROM $(vTable) $(vWhere);

Table:

SQL $(vSQL);

Re: Variable in SQL

Fri, 15 May 2015 11:52:56 GMT

However "@SQL" is a variable in SQL, not in Qlikview!

Re: Variable in SQL

buzzy996 — Fri, 15 May 2015 11:56:00 GMT

i mean in qlikview sql script u have to use $ symbol..if u want to use the variable.

Re: Variable in SQL

Clever_Anjos — Fri, 15 May 2015 13:48:26 GMT

Much easier if you move all your dynamic logic into a procedure

set vtable = 'TAB01'

SQL Exec '$(vtable)'

Useful reading:

https://technet.microsoft.com/en-us/library/aa225239%28v=sql.80%29.aspx

PL/SQL Language Elements

Re: Variable in SQL

Fri, 15 May 2015 13:52:50 GMT

I got this way:

set xpto = '';

let counter = 1;

let xempa = '010060';

do while counter < 3

let xemp = mid($(xempa),1,3);

if $(counter) > 1 then

set xpto = $(xpto) union all ;

set xpto = $(xpto) Select top 10 $(counter) as emp, E1_NUM from SE1$(xemp);

Else

set xpto = Select top 10 $(counter) as emp, E1_NUM from SE1$(xemp);

Endif

let counter = counter + 1;

let xempa = mid($(xempa),4,len($(xempa))-3);

loop

teste:

SQL

$(xpto);

Thanks a lot everybody!