I put the parenthesis because MySQL wasn't accepting the order by in the first select. It was far from a good solution but seemed slightly better than before and allowed to test for more issues on multiple measure groups.<br>
<br>Meanwhile I made it output only one order by for the whole union as you suggest. Before it was not just generating an order by for each select, but the last one would have two, both by column name and by ordinal. Now it only does the ordinal order for unions<br>
<br>It's one of the commits in the last pull request I made:<br><br><a href="https://github.com/webdetails/mondrian/commit/e37cc17f6a112da3639e5591c5a7de193c368766">https://github.com/webdetails/mondrian/commit/e37cc17f6a112da3639e5591c5a7de193c368766</a><br>
<br>From what I tested it runs ok in MySQL, Hypersonic and Oracle.<br><br><div class="gmail_quote">On 4 December 2012 07:04, Julian Hyde <span dir="ltr"><<a href="mailto:jhyde@pentaho.com" target="_blank">jhyde@pentaho.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">By the way, this was the commit: <a href="https://github.com/pentaho/mondrian/commit/8837434cc059a0023da25288abfe5e9ec21f38fc" target="_blank">https://github.com/pentaho/mondrian/commit/8837434cc059a0023da25288abfe5e9ec21f38fc</a>.<br>
<br>
Julian<br>
<div><div class="h5"><br>
On Dec 3, 2012, at 10:44 PM, Julian Hyde <<a href="mailto:jhyde@pentaho.com">jhyde@pentaho.com</a>> wrote:<br>
<br>
> Tiago,<br>
><br>
> What was the error that made you make the following change, and on what database?<br>
><br>
> Before your change, the code used to generate<br>
><br>
> SELECT ... ORDER BY UNION SELECT ... ORDER BY<br>
><br>
> and after your change generates<br>
><br>
> (SELECT ... ORDER BY) UNION (SELECT ... ORDER BY)<br>
><br>
> ORDER BY inside a subquery is illegal per the SQL standard (and as it happens makes MongoDB's parser barf), so I wonder if that was the real problem you were facing. I am fixing it to generate<br>
><br>
> (SELECT ...) UNION (SELECT ...) ORDER BY<br>
><br>
> and, since simpler is better, I wonder if I could go one step further and generate<br>
><br>
> SELECT ... UNION SELECT ... ORDER BY<br>
><br>
> To help me decide, it would help to know why you added the parentheses in the first place.<br>
><br>
> Julian<br>
</div></div>> _______________________________________________<br>
> Mondrian mailing list<br>
> <a href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a><br>
> <a href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
<br>
</blockquote></div><br><br clear="all"><br>-- <br><div>Tiago G.F.</div><br>