Unexpected ';' in SQL statement

Unexpected ';' in SQL statement


I was doing an advanced sql and after testing it and getting the correct output I've noticed the the following warning:

Unexpected SQL
Unexpected ';' in SQL statement of AdvQuery1.

This is my query:
;with Movimento as (
SELECT {RegistoDeposito}.[Data], -{RegistoDeposito}.[Quantidade] as Quantidade
  from {RegistoDeposito}
union all
SELECT {Enchimentos}.[Data], {Enchimentos}.[Quantidade]
  from {Enchimentos}
SELECT [Data], Sum(Quantidade) as Quantidade
  into #MovSeq
  from Movimento
  group by [Data];

-- cria índice para agilizar pesquisa por data
CREATE clustered INDEX I1 on #MovSeq ([Data]);  -- or unclustered with include (Quantidade) ??

SELECT convert(date, [Data]), Quantidade= (SELECT Sum(Quantidade) from #MovSeq as M2 where M2.[Data] <= M.[Data]), '', '', ''
  from #MovSeq as M
  order by [Data];
it gives me the error on the ; and on the with as well.

Also, is this why the SQL isnt working to make a chart? because for some reason its not even though the ouput seems legit.
I made a simple ;with statement and here are some more details:

  1. In Studio it is not an error, but a warning.
  2. You can start the Advanced Query with "with" and drop the ";", the simptoms are the same both in Studio (works) and Service Center (doesn't).
  3. Service Center has an error: "Could not calculate the query count" (probably the count for table where I show lines)
I would say you can use a previous Advanced Query that returns a structure to remove that problem, but it seems a bug.
When I remove the ; I get the same thing but with with yeah. I've tried to set this query into a record list and make the append but the graphic showed without any record. I'm honestly used to set the output as a datapoint :x

Honestly the only time I made it work was like the chart tutorial, but that only appends 1 Label right? And here I have date on X and the quantity on Y. Could you help me set it up to test?
Hi Cristiano,

The OutSystems Platform will always issue a warning because it expects to have only one statement in the advanced query. If you get the query to output the results fine, just ignore the warning. Just confirm with debug that you have a record list with the data points in the end. If you get the list of datapoints then this is not the reason why your chart isn't working... check if you have any javascript error on client side using the browser developer tools.
André, it isn't only that. The query simply doesn't work. Only works in service studio.
If I press Test in service studio I get the correct output. but I used a breakpoint after the advancedquery and there is nothing in the list. 
Greetings, I was wondering if anyone can help me find a work around for this :x 

try removing the -- comments from the query 
I had issues with them before 
only use /* comments */ style

ive also used with queries successfully 

Hey wait a sec...
best viewed with eyes open

If the output structure is a data point then pass null to the attributes of the structure not in use
 (Color, tooltip etc)

are you trying to create an index in an advanced query for a temp table? Wow never saw that before..,

The database runtime user only has data reader and data writer permissions. 

Creating a a normal index would fail (it needs the Ddl admin) so I'm also not sure if creating an index on a temp table would work. But there should be plenty of error messages. 

Interesting use case

Keep us posted 

Sorry about the delay. I've deleted the comments but still have the same issue.

As I've said before if I press Test on the Service Studio I get the correct output, if I debug or test it live the Query returns 0 results.

Btw, the output is already formated to fit the data point (the last select does it)
Apparently this got fixed in the last version by itself :O

Tried to find the changelog for it but nothing.