Passing a variable from input box to SQL

[#1]

Hi everybody,

I am trying to assign a value to a variable by creating an input box on the sheet, and then using that variable in the SQL select statement as a constraint.

I believe I have the variable correctly. When I go to the variable tab at the bottom of the edit script I see the value of basTarih as 2012-10-01.

When I try to run the code this way I get an error saying that it cannot convert basTarih to timestamp. In the onput box properties number tab I've selected both date and timestamp formats for basTarih but none seem to work.

Do I need a SET statement somewhere?

siparis:

LOAD *;

SQL SELECT ol_or_no1, ol_or_no2, ol_sr, ol_ma_kod AS ma_kod, ol_qty, ol_kapa, ol_ds_kod AS desen, ol_vry AS varyant, ol_plan_onay, date(ol_wrk_basdt), date(ol_onaydt),date(ol_terdt),

                               ol_or_anhno, ol_onay2, date(ol_onay2dt),or_pe_no, or_anhno, or_rr_no

          FROM DBA.ordln

          JOIN DBA.ord

          ON ordln.ol_or_anhno = ord.or_anhno

          WHERE ol_kapa = 'A' AND (date(ol_onaydt) BETWEEN '&basTarih' AND '2012-10-08')

          order by ol_or_no2, ma_kod, desen, varyant

;

Thanks for your help.

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

If the variable already exists in your input box in the design, you can check its contents in the Settings menu, Variable Overview. If that's OK, then the SQL should look like the following:

WHERE ol_kapa = 'A' AND (date(ol_onaydt) BETWEEN '$(basTarih)' AND '2012-10-08')

So QlikView returns the value of the variable and so it's passed on to the ODBC driver and the database.

Hope that helps.

Miguel

[#3]

Thanks Miguel, I've initially tried the $(basTarih) but I forgot he single quotes, thanks again.