Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

johanfo2
Contributor

Multiple statements in SQL query

Hi,  I have a quick question, which I suspect the answer to is No.

Can you, in Qlikview, directly execute SQL statements like this:

--------------------

LOAD *;

SQL

declare @maxdate date;

select @maxdate =  max(MyColumn) from Holdings

select * from AnotherTable Where Column < @maxdate;      

---------------------

In MSQL Server Management Studio they run just fine, but I believe there are restrictions on these multiple statements over ODBC, and that they need to be converted to stored procedures serverside?

Can anybody confirm this?

Thank you.

J

1 Solution

Accepted Solutions
MVP
MVP

Re: Multiple statements in SQL query

- you can use stored procedures

- you can change your statement in TSQL e transform it in a sigle query

- or split your TSQL script in that way in qlikview

Tmp:

LOAD *;

SQL SELECT max(MyColumn) as MaxField from Holdings;

// set a qlikview variable using peek function

LET vMaxDate = peek('MaxField', 0, 'Tmp');

DROP TABLE Tmp;

// use that variable to filter the select in sql server

LOAD *;

SQL

select * from AnotherTable Where Column < '$(vMaxDate)';

     

4 Replies
Luminary
Luminary

Re: Multiple statements in SQL query

Using server side stored procedures and calling them from the QlikView script including passing variables definitely works.

MVP
MVP

Re: Multiple statements in SQL query

- you can use stored procedures

- you can change your statement in TSQL e transform it in a sigle query

- or split your TSQL script in that way in qlikview

Tmp:

LOAD *;

SQL SELECT max(MyColumn) as MaxField from Holdings;

// set a qlikview variable using peek function

LET vMaxDate = peek('MaxField', 0, 'Tmp');

DROP TABLE Tmp;

// use that variable to filter the select in sql server

LOAD *;

SQL

select * from AnotherTable Where Column < '$(vMaxDate)';

     

MVP
MVP

Re: Multiple statements in SQL query

Hi

the problem is that QV will see the semi-colon (Smiley Wink as ending the SQL statement. I have not tried this, but perhaps:

declare @maxdate date

GO

select @maxdate =  max(MyColumn) from Holdings

select * from AnotherTable Where Column < @maxdate;     

will work? If not, you can package them in a stored proc and call them with


SQL EXEC ....

HTH

Jonathan

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

Re: Multiple statements in SQL query

I tried to "GO" approach now,  didn't work. Thanks a lot for your answers.  I guess they confirmed my suspicions, and that is good.

J