Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

SqlValue

Hi there,

I'm trying to use SqlValue in order todo a select on a database as, even if i ignore it, we can't do a sql query (in an expression) on a resident table into qlikview.

As i want to keep the more flexity as possible, and as my data are quite complex, i can't and i don't want to load this data on the load.

The expression i use is:

=SqlValue('10.20.26.221:7658','q) select VAL from FX where CUR1=`GBP,CUR2 =`EUR,DATE =2009.05.14;','OLEDB')

The "q)" statement is used to do a query on KDB database.

Unfortunately, this sqlValue query gives no result:""and no error.

I so tried to run the same query using the script on the load, which give me the good result:

CONNECT

TO [Provider=MSDASQL.1;Persist Security Info=False;Data Source=10.20.26.221:7658;Extended Properties="DRIVER=kdb+;DBQ=10.20.26.221:7658;UID=pricefeedservice;DSN=default;"];

LOAD

VAL;



SQL

q)select VAL from FX where CUR1=`GBP,CUR2 =`EUR,DATE =2009.05.14;

It may be a connexion problem but i don't succeed in resolving it.

Has anyone already have some troubles with SqlValue?

Thanks for your help.















1 Solution

Accepted Solutions
Not applicable
Author

I found the solution.

Everything is righ in my query in fact.

It just seems that for numerical values, SqlValue return "".

So if you want to have your value, i foudn a way to do it. You just have to do a calculation using this SqlValue. I mean:

=0+SqlValue(....) or =1*sqlValue(...) or whatever.

However, such a behaviour is really strange.

View solution in original post

4 Replies
rbecher
Luminary Alumni
Luminary Alumni

Hi,

I think there is something wrong with the qoutes and the WHERE clause. It should be in SQL SELECT statement:

select ... where CUR1='GBP' and CUR2='EUR' and DATE='2009.05.14'; // date format depends on database vendor

and in QV script:

sqlvalue('<ODBC name of the connection>', 'select ... where CUR1='&chr(39)&'GBP'&chr(39)&' and CUR2=&chr(39)&''EUR'&chr(39)&' and DATE=&chr(39)&'2009.05.14'&chr(39))

But SqlValue() only retrievs the first value of the first column. I wouldn't use this, only to check if the connection is working and definitely not as an expression.

Ralf

Vizlib Head of R&D
Not applicable
Author

Hi,

In fact the query must be the one i have written above.

It was not real SQL as the KDB database use it's own langage call "q" which need to have this syntax.

rbecher
Luminary Alumni
Luminary Alumni

..but, you´re sure this also works via ODBC? You could give it a try.

Vizlib Head of R&D
Not applicable
Author

I found the solution.

Everything is righ in my query in fact.

It just seems that for numerical values, SqlValue return "".

So if you want to have your value, i foudn a way to do it. You just have to do a calculation using this SqlValue. I mean:

=0+SqlValue(....) or =1*sqlValue(...) or whatever.

However, such a behaviour is really strange.

View solution in original post