Multiple statements in SQL query

[johanfo2]

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

[simondachstr]

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

[maxgro · Accepted solution]

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

[jonathandienst]

Hi

the problem is that QV will see the semi-colon (;) 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

[johanfo2]

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