Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Variable in SQL

SET vtable = TAB01;

Table:

SQL Select * From $(vtable);


talk is cheap, supply exceeds demand
buzzy996
Honored Contributor II

Re: Variable in SQL

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

Not applicable

Re: Variable in SQL

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

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

Re: Variable in SQL

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

buzzy996
Honored Contributor II

Re: Variable in SQL

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

Employee
Employee

Re: Variable in SQL

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

Re: Variable in SQL

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!

Community Browser