Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Variable in SQL

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

8 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

SET vtable = TAB01;

Table:

SQL Select * From $(vtable);


talk is cheap, supply exceeds demand
buzzy996
Master II
Master II

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

Not applicable
Author

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!

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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);


talk is cheap, supply exceeds demand
Not applicable
Author

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

buzzy996
Master II
Master II

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

Clever_Anjos
Employee
Employee

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

Not applicable
Author

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!