topic SqlValue in QlikView

SqlValue

Mon, 18 May 2009 22:15:18 GMT

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.

SqlValue

rbecher — Mon, 18 May 2009 23:12:31 GMT

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

SqlValue

Mon, 18 May 2009 23:21:44 GMT

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.

SqlValue

rbecher — Mon, 18 May 2009 23:44:47 GMT

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

SqlValue

Wed, 20 May 2009 18:22:40 GMT

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.