Discussion Board for collaboration related to QlikView App Development.
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:
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.
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
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.
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.
..but, you´re sure this also works via ODBC? You could give it a try.