topic Re: Multiple statements in SQL query in QlikView

Multiple statements in SQL query

johanfo2 — Mon, 24 Feb 2014 11:31:59 GMT

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.

Re: Multiple statements in SQL query

simondachstr — Mon, 24 Feb 2014 12:13:41 GMT

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

Re: Multiple statements in SQL query

maxgro — Mon, 24 Feb 2014 12:28:10 GMT

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

Re: Multiple statements in SQL query

jonathandienst — Mon, 24 Feb 2014 12:31:15 GMT

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

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

Re: Multiple statements in SQL query

johanfo2 — Mon, 24 Feb 2014 13:04:01 GMT

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.