How to set variables in advanced queries

How to set variables in advanced queries

  

Hi everyone,


Is there a way for me to use the 'SET' Clause to declare variables in Advanced queries?


For example I want to declare the variable row_number:


And I get the following error when hitting Test: 


I even added an input parameter with the same name to the query (set it as expand inline) and I still get the same error.


Let me know if you have ever been able to do something similar.


Thanks in advance.


Ricardo



Hi Ricardo,


Have you tried the SET statement without the ";" at the end? 

I tested this simple query and it didn't return any errors.

Let me know if this helps. 

I'll leave this link if you still have some doubts. https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-local-variable-transact-sql?view=sql-server-2017

Best regards,

Davide Résio

Hi David,

Thanks for the reply.

What did you set as the test input for the 'row_number' variable? And is it set as text with the Expand Inline option set to yes?


I get the following error now:


Cheers,

Ricardo

Remove ; at end and try hope it works

Ricardo,


From the link that I left previously, the information that is given is that your variable "@row_number" can't be of the text type:

"@ local_variable
Is the name of a variable of any type except cursor, text, ntext, image, or table..."


I set mine to integer type and left the test input blank.

What exactly are you trying to achieve by setting your input parameter via a SET statement?


Best regards,

Davide Résio

Davide Résio wrote:

Ricardo,


From the link that I left previously, the information that is given is that your variable "@row_number" can't be of the text type:

"@ local_variable
Is the name of a variable of any type except cursor, text, ntext, image, or table..."


I set mine to integer type and left the test input blank.

What exactly are you trying to achieve by setting your input parameter via a SET statement?


Best regards,

Davide Résio

I actually forgot to mention something important: I am using MySQL. After doing what you suggested I still get a syntax error:



Since Row_Number doesn't exist in MYSQL I am trying to implement it manually using the following link as a reference: http://www.mysqltutorial.org/mysql-row_number/


Cheers,

Ricardo

Ricardo Freitas wrote:

Davide Résio wrote:

Ricardo,


From the link that I left previously, the information that is given is that your variable "@row_number" can't be of the text type:

"@ local_variable
Is the name of a variable of any type except cursor, text, ntext, image, or table..."


I set mine to integer type and left the test input blank.

What exactly are you trying to achieve by setting your input parameter via a SET statement?


Best regards,

Davide Résio

I actually forgot to mention something important: I am using MySQL. After doing what you suggested I still get a syntax error:



Since Row_Number doesn't exist in MYSQL I am trying to implement it manually using the following link as a reference: http://www.mysqltutorial.org/mysql-row_number/


Cheers,

Ricardo

Yeah, the syntax for MySQL is different. In the this version of your code you forgot the "SET" before "@row_num = 0" and, because you're using MySQL you need the ";" at the end of the statement.

Try this suggestion to implement the incremental counter: https://stackoverflow.com/questions/13566695/select-increment-counter-in-mysql


Best regards,

Davide Résio

Yea I forgot to add SET in that last screenshot but I tried it with the SET and was still unable to test the query successfully. I also tried doing it as suggested in the website you provided and I either get a syntax error or it says that the variable has not been created. I am going to try a few more things but thanks for all of your suggestions so far.

Hi Ricardo, If defining a variable absolutely does not work, you can use a cte to achieve your desired effect. Ctes can reference themselves,  so you can fetch one record at a time and keep incrementing your counter. This is,  however,  a very complex construct to achieve such a simple result and probably not performant at all but it should do what you want if you find no other way. 

Afonso Aguas wrote:

Hi Ricardo, If defining a variable absolutely does not work, you can use a cte to achieve your desired effect. Ctes can reference themselves,  so you can fetch one record at a time and keep incrementing your counter. This is,  however,  a very complex construct to achieve such a simple result and probably not performant at all but it should do what you want if you find no other way. 

For now I am using a for each cycle after the query to add the rownumber by using the "CurrentRowNumber" attribute. Not the most performant way to do it but it works.


Now here comes a dumb question, what is cte? :)


Ricardo Freitas wrote:

Afonso Aguas wrote:

Hi Ricardo, If defining a variable absolutely does not work, you can use a cte to achieve your desired effect. Ctes can reference themselves,  so you can fetch one record at a time and keep incrementing your counter. This is,  however,  a very complex construct to achieve such a simple result and probably not performant at all but it should do what you want if you find no other way. 

For now I am using a for each cycle after the query to add the rownumber by using the "CurrentRowNumber" attribute. Not the most performant way to do it but it works.


Now here comes a dumb question, what is cte? :)


That is probably your best solution as of now. 


There are no dumb questions :) you can read about cte's here https://dev.mysql.com/doc/refman/8.0/en/with.html


Hey,

Did you try do some like this:


SELECT 0 INTO @row_num FROM DUAL; --> This works for Oracle


But look, if the "row_num" is from integer type, the default value will be always 0.


Regards,

Nelson

Ricardo, 

You have other way, just do it in a Storage Procedure and call the SP