modifying a variable in a SQL request under QlikView

[#1]

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

[gsbeaton]

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

[#3]

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:

1. LET cbid = 0; 
3. Table1: 
4. SQL 
5. SELECT * FROM 
6.     (SELECT *, 
7.           (100*BIDS/TOTAL_BIDS) as PERCENT_BIDS, 
8.           ($(cbid)=MOD($(cbid)+100*(BIDS/TOTAL_BIDS),100)) as COLUMN8 
9. from 
10. ... 

Because, in this case I guess QlikView intreprets the $(cbid) as a value and not a variable.

[jonathandienst]

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.

[jonathandienst]

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.

[gsbeaton]

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

[gsbeaton]

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

[#8]

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

[#9]

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

[PradeepReddy]

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