Recursive query in Advanced Query Service Studio return Parameter hasn't been created

Recursive query in Advanced Query Service Studio return Parameter hasn't been created

  

I want to create a query that's returning all lines that has a recursive id.

Value:

ID  |  ReferenceId  |  Value

10  |  null  |  150

11  |  10  |  100

12  |  null  |  400

13  |  11  |  300


A query like this works:

select t.Id, t.value, @pv := t.ReferenceId
from (select * from random_table_name order by Id desc) t
join (select @pv := 10) tmp
where t.Id = @pv;

This code works in Mysql and it returns the rows with id 10,11 and 13.


But I can't get this in Service Studio... He keeps messing with validations about paramters. I tried to solve it by declaring a variable with replace inline, the value will be the name of the variable. Seems to work for others but I still get the error "Error in advanced query Name: Paramter @pv has not been created"

Does someone know how to solve this. Otherwise I am forced to iterate through the records using the webserver :( and that's not nice for the performance.

Hi Peter,

Though I don't understand at all what you are trying to achieve here (i.e. I have never done something like that, nor do I understand the query), I have in the past used replace-inline parameters containing a variable name. Do you have the possibility to look at what the database server actually receives? You may be able to spot what goes wrong then.