Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I am trying to use a mysql request which contains a variable (in this case the variable is @cbid). It seems that this variable is not taken into account when I run it in QlikView. I think there is a syntax error with the variable, but I don't see which one.
The request starts like this:
set @cbid:=0; |
SELECT * FROM
(SELECT *,
(100*BIDS/TOTAL_BIDS) as PERCENT_BIDS,
(@cbid:=MOD(@cbid+100*(BIDS/TOTAL_BIDS),100)) as COLUMN8
from
...
Is anyone able to help me?
Thank you by advance,
Sincerely ,
Nicolas
Hi Nicolas,
It's a while since I worked with mySQL variables, but from memory, your ability to set a variable and reuse it in this way will largely depend on your ODBC driver.
However, if you are writing this script in QlikView, to then be run in mySQL, why not inject in the variables the QlikView way
LET cbid = 0;
Table1:
SQL
SELECT * FROM
(SELECT *,
(100*BIDS/TOTAL_BIDS) as PERCENT_BIDS,
($(cbid)=MOD($(cbid)+100*(BIDS/TOTAL_BIDS),100)) as COLUMN8
from
...
Hope that helps
George
Hi George,
Thank you for your answer, but unfortunatly it doesn't work when I try it on my computer. It gives me a "0" value for each row of the "COLUMN8" while it is not like this on the MySQL results...
I think the bold part here must be written in another way, but I don't know how:
Because, in this case I guess QlikView intreprets the $(cbid) as a value and not a variable.
You are corrrect. Qlikview will 'expand' the variable to '0' before passing the query to the MySQL DBMS.
You may need to code this another way. Qlikview passes everything from the SQL keyword up to the ; to the DMBS and I assume that the variable definition does not apply to the subsequent SQL SELECT.
Perhaps you could place the whole query into a variable like this:
Set vQuery =
'set @cbid:=0;
SELECT * FROM
(SELECT *,
(100*BIDS/TOTAL_BIDS) as PERCENT_BIDS,
(@cbid:=MOD(@cbid+100*(BIDS/TOTAL_BIDS),100)) as COLUMN8
from
...
';
(replace the ellipsis ... with the rest of your SQL expression)
And then use it like:
MyData:
SQL $(vQuery);
This should allow the ; to be embedded in the SQL statement. Note that this a SET statement, not a LET.
Hi Nicolas,
Okay, I think I misunderstood what you were trying to do here. What I thought you were trying to do was in effect create an SQL statement with a variable value. In that case the appraoch I suggested would work. As Jonathan states, the variable gets parsed before the statement is set to your database. Just to prove the point I changed your value to 33. Here are the log results:
09/02/2016 14:19:09: 0013 LET cbid = 33
09/02/2016 14:19:09: 0015 Table1:
09/02/2016 14:19:09: 0016 SQL
09/02/2016 14:19:09: 0017 SELECT * FROM
09/02/2016 14:19:09: 0018 (SELECT *,
09/02/2016 14:19:09: 0019 (100*BIDS/TOTAL_BIDS) as PERCENT_BIDS,
09/02/2016 14:19:09: 0020 (33=MOD(33+100*(BIDS/TOTAL_BIDS),100)) as COLUMN8
09/02/2016 14:19:09: 0021 from
09/02/2016 14:19:09: Error: ODBC connection failed
09/02/2016 14:19:11: Execution finished.
However, I think what you are trying to do is set a QlikView variable based on the value of a mySQL variable? Can you confirm? This is not possible and you will have to follow a different approach.
You would have to run your SQL and then use PEEK() to get the value of your COLUMN8 into a variable, eg
LET v_cbid = PEEK('COLUMN8', 0, 'Table1');
I'm still not entirely convinced I have a grasp of what you are trying to do now, so please give me a better steer if I am off the mark.
Kind regards
George
Hi Nicolas,
Are you any further forward? This thread keeps popping up in my inbox. I'm convinced now that this is not a QlikView issue, and more likely a logic issue with either what you are trying to do with your ODBC driver or your SQL itself. From what I can see, you are trying to set a mySQL variable and then execute it (two statements) in a single SQL statements, and then somehow have the mySQL variable return its value to the QlikView variable. If you can be a bit more specific about what it is you are trying to do, you might get a better response here.
Kind regards
George
You need to use $ sign to interpret variable value
Try like below:
LET @cbid:=0; |
SELECT * FROM
(SELECT *,
(100*BIDS/TOTAL_BIDS) as PERCENT_BIDS,
($(@cbid:)=MOD($(@cbid:)+100*(BIDS/TOTAL_BIDS),100)) as COLUMN8
from
I just tested out you can't use colon ":" to you variable name. So try like below:
LET @cbid = 0 ;
SELECT * FROM
(SELECT *,
(100*BIDS/TOTAL_BIDS) as PERCENT_BIDS,
($(@cbid)=MOD($(@cbid)+100*(BIDS/TOTAL_BIDS),100)) as COLUMN8
from
try something like this..
Let @cbd=0;
SELECT * FROM
(SELECT *,
(100*BIDS/TOTAL_BIDS) as PERCENT_BIDS,
((@cbid)=MOD($(@cbid)+100*(BIDS/TOTAL_BIDS),100)) as COLUMN8
from