Order by is not working in procedure - QUERY object

Help Procedural SQL
1

Hi Team,

Why order by clause is not working properly when am using in stored procedure ?

am i missing something? or memsql does not support order by clause?

Kindly help me resolve this issue .

Note:

I have written dynamic query in QUERY object using CONCAT (Because am passing tablename dynamically)
My query is having WITH CLAUSE , UNION ALL.

2

QUERY type values (QTVs) are like dynamic view definitions, and ORDER BY in a QTV is thus ignored if there is no LIMIT clause, since a view is logically a rowset without a specified order. Suppose you have a QTV like this:

declare q query(…)= SELECT a, b from t order by b;

Then if you want ordering on b in your final result, you need to do something like this:

echo select a, b from q order by b;

3

What is the use of echo here?

I need to store the output of QUERY to RECORD type variable like below

qry QUERY(a int,b int,c varchar(25) )= (Select a,b,c from t order by c,a);

arr ARRAY(RECORD(a int,b int,c varchar(25)));

arr=COLLECT(qry);

Then i need to process arr variable in FOR LOOP to process record by record.

But problem is ORDER BY CLAUSE is not working here. in my case ORDER BY is very important.

How can i solve this issue when am doing this kind of coding?

Please help