topic Re: variables in sql in QlikView

variables in sql

Mon, 07 Sep 2015 10:12:29 GMT

Hello;

I want to execute an sql script using a qlikview variable. I explain what I want with an example:

I have a qvw file called MyQVW.QVW with the variable named vDocumentName with the value DocumentName()

The script calls a SQL file:

CUSTOMER:

SQL $(Include=CUSTOMER.sql);

In the SQL file "CUSTOMER.sql" I want to use the variable vDocumentName like this way, because I use this SQL file in many QVW and I only want that return values when the QVW is MyQVW.QVW:

SELECT *

FROM tb_Customers

WHERE 'MyQVW.QVW'='$(vDocumentName)'

The script doesn't do what I want. If I use '$(vDocumentName)' in a field of the SQL file (EJ: TEST='$(vDocumentName)') returns correctly the value of the name of the file but when I use it in an expression "'MyQVW.QVW'='$(vDocumentName)'" this doesn't go well.

Someone knows what happens in my script?

Thank you,

Fernando

Re: variables in sql

alexandros17 — Mon, 07 Sep 2015 10:34:32 GMT

try with

SELECT *

FROM $(vDocumentName)

let me know

Re: variables in sql

sasiparupudi1 — Mon, 07 Sep 2015 10:45:52 GMT

May be Try

if DocumentName()='MyQVW.QVW'

tab1:

SELECT *

FROM tb_Customers;

end if

Re: variables in sql

Mon, 07 Sep 2015 10:52:00 GMT

Hello Alessandro;

This is not what I want. I want to do a select from tb_Customers.

Thanks,

Fernando

Re: variables in sql

Mon, 07 Sep 2015 10:56:34 GMT

Hello Sashidar;

The SQL file is a UNION of many SELECTs. In this select only have to return values if the document is MyQVW.

I need to have the SQL Script in a file and call it with an include sentence of QlikView.

This solution is not for my problem

Thanks,

Fernando

Re: variables in sql

sasiparupudi1 — Mon, 07 Sep 2015 12:59:52 GMT

can you please post a sample app with your sql file?

I am not sure if it is your expectation to write qv script in a text file? if it is then it is not going to work.

Sasi

Re: variables in sql

Mon, 07 Sep 2015 13:56:03 GMT

Hello Sasi;

I have the sql script in a text file, called CUSTOMER.sql

I call it in the qlikview script of many files qvw with the next sentence:

SQL $(Include=CUSTOMER.sql);

I want to use the variable vDocumentName in the sql script.

I hope I have explained well.

Thanks,

Fernando

Re: variables in sql

Peter_Cammaert — Mon, 07 Sep 2015 14:44:48 GMT

You have a SQL problem here. If I understand you well, sometimes you do want the SELECT from Customers to return rows by adding something like ...WHERE 1 = 1 (simplified) and at other times you do not want the SELECT from Customers to return rows by saying something like ... WHERE 1 = 0. Which works perfectly in a QlikView LOAD statement but aparently not in SQL. Right?

Peter

Re: variables in sql

Mon, 07 Sep 2015 15:06:25 GMT

Hello Peter;

I wanted to simplify the SQL script because I thought that would be easier to understand the problem but got the opposite effect.

My SQL have many conditions and many joins.

Really I want to do something like this:

SELECT *

FROM table1 as t1

INNER JOIN table2 as t2

ON t1.id=....

....

WHERE condition1=true

and condition2 =false

.....

and ( ('MyQVW.QVW'<>'$(vDocumentName)')

(t1.Date >= '07/09/2015')

)

If the QVW file is MyQVW.QVW the condition have to evaluate the condition t1.Date >= '07/09/2015' and only will give me the data of today

If the QVW file isn't MyQVW.QVW the condition is always true and don't will evaluate the condition t1.Date >= '07/09/2015' and will give me all of data

But 'MyQVW.QVW'<>'$(vDocumentName)' don't return the expected result.

Thanks,

Fernando