Compound outputs from SQL advanced CTE query

Compound outputs from SQL advanced CTE query

  
I am using a SQL advanced query for a recursive CTE query. The list output is rendered to a screen that has the List_Navigation widget set.  Those require a count of the records returned by the query. I can't use List.Count because that doesn't work with SQL CTE queries (as confirmed in other support/forum posts).  I also ideally don't want to have two queries in my Preparation (one for the query itself and one for counting the records returned by the query), since that would require duplication of the query definition SQL and risk of the two becoming different from each other (ie bugs), plus it requires running the CTE twice (slow).

So I'd ideally like to have two resultsets from the single SQL query - one with the records and one being a Structure with a single attribute and record for the count of the query's resultset.  I am able to add a second Output Structure to the query no problem.  I am also able to add a second SELECT statement to the SQL without getting syntax error complaints (it won't accept GO but seems to accept ";" between the statements).  However no matter which order I put the Output Structures in, within the SQL editor treeview, I get a complaint both when running Test and when deployed (ie via the browser) that the columns don't match the outputs.  I'm pretty sure my columns do match because: my new RowCount structure has a single attribute; my second SELECT returns a single column; and when I remove both the RowCount structure and the second SELECT statement there are no such complaints.

So, question is, how are we intended to use compound outputs within a SQL advanced query?  Can't find any help, and almost no forum posts, on this.
Hi Paul,

You can't do what you want. Afaik only the last output is output to your output structure. I'm also not sure why you think having two queries in one advanced query is better than having to separate advanced queries. That still allows for updating one and not the other, creating the same bugs. You could add some comment like "KEEP THIS TWO QUERIES IN SYNC!!!! (BY ORDER OF DEATH!!!11!1!!11ONE!11!!)".

Also, depending on your particular use case, you could just retrieve all records at once, not bothering with the performance improvement by limiting the output to the number of rows in a table records widget (if that's what you're using). This is especially recommendable if the query itself is complicated, but the output structure is small (in which case it's the query execution time, not the time needed to pass the data back, that's the bottle neck), since it saves you the count query. To retrieve the number of records, in that case just use query.List.Length (i/o query.Count).



Hi Kilian.  The improvement is that I wouldn't have to write the CTE twice (just repeat add a very simple SELECT Count ([Id]) at the bottom of the main query.  Also, I haven't been able to get query.List.Length to work, hence raising this question.
Hi Paul,

What do you mean by "haven't been able to get query.List.Length to work"? Did you experience that it returns the Table Records length + 1 (which is the default behaviour)? You could try to explicitly set the maximum number of records to a very high number, like a million, in which case the platform will retrieve all records.