The usage of declare in Qlikview sql statements

[#1]

Hi,

this is my first post here.

Im working with Qlikview development for some time now, and right now im working on analysis tools for our new SQL-based system.

I want to get data from our SQL server with a variable in the statement which i want to declare on the SQL-server in the Qlikview script. Because my company is working with large databases i would like to keep the data transfer to qlikview as small as possible

What i try to do in the code below is declare a variable in SQL statement, and use it in the select statement below it.
What Qlikview does is declare the variable and give it the value of today, but it skips the entire select statement

(in this case, this should differ. i want to learn how it works, im not interested in a specific method.)

im working with Qlikview 9 and SQL server 2008. (the SQL script does work on SQL server 2008)

(qlikview script)
...
SQL
declare @Vandaag datetime
set @Vandaag = CAST({fn NOW()} as datetime)
-- the qlikview script doesnt seem to run after this line
SELECT
Partij.ID as Partijnummer,
CONVERT(VARCHAR(10),CAST(Partij.Datum as datetime),105)
 as Stringpartijdatum,
Partij.Datum as Partijdatum,
Partij.Eindinkoopprijs as [Originele prijs],
PartijVestiging.ID as KeyVerkoopteam,
Prijsverschil.Eindinkoopprijs as NieuwEindinkoop,
Prijsverschil.ID as Subpartijnr,
(Prijsverschil.Eindinkoopprijs - Partij.Eindinkoopprijs)
 as Prijsverschil
FROM (Partij Partij
LEFT JOIN Vestiging AS PartijVestiging on PartijVestiging.ID=Partij.VestigingID)
INNER JOIN Partij AS Prijsverschil ON Prijsverschil.HoofdpartijID=Partij.ID
WHERE PartijVestiging.ID <> 0
AND Partij.Eindinkoopprijs > Prijsverschil.Eindinkoopprijs
AND Partij.Datum < @Vandaag
;
..
(qlikview script)

[sparur · Accepted solution]

Hi, Mark

Try that:

LET vNow = now();

LOAD

Partijnummer,

Stringpartijdatum,

Partijdatum,

[Originele prijs],

KeyVerkoopteam,

NieuwEindinkoop,

Subpartijnr,

Prijsverschil

;

SQL SELECT

...

WHERE .... AND

Partij.Datum < $(vNow)

[#3]

Hi sparur,

thanks for your answer.

It works with declaring Qlikview variables and using them in sql scripts.

i was just wondering if it was possible to decare and use variables server side... and would it decrease the amount of data transferred from the SQL server to qlikview? (so the server has less data to transfer)

[sparur]

As I know, when you use clause WHERE in SQL statement (as I post) SQL server must to transfer only decrease the amount of data to qlikview.

Yo Don't need to declare a variable in SQL Server. because SQL Server get sql query with your range (in example range by date).

[#5]

Of course.. thanks.

I understand now why i wouldnt want to declare a variable in SQL script.

[sparur]

I was glad to help you to understand this situation.

If not difficult to verify my first post as answer.

best regards, Anatoly.

[#7]

Of course.. thanks.

I understand now why i wouldnt want to declare a variable in SQL script.