Re: Inconsequences in SELECT/ORDER BY

In article <2nttv6$3r4@maicgn.SoftwareEngineering>, ar@mai.de (Achim Reiners) writes: |> Hello Informixers! |> |> I had a little problem with an ORDER BY clause: |> |> First of all I use the possibility to SELECT a column more than once |> in a SELECT-statement: |> |> SELECT col_a, col_b, col_a FROM tab; |> |> This works fine and makes life easier for me (really!). |> |> Now I tried: SELECT col_a, col_b, col_a FROM tab ORDER BY col_a; |> |> I received the "ambiguous column" - error. I think that in this context |> this error should NOT be reported. |> |> O.K. the workaround is quite easy using "display labels". |> ( SELECT col_a ca1, col_b, col_a ca2 FROM tab ORDER BY ca1; ) |> |> What is the opinion of the net? Is the error reporting correct or not? |> |> |> Achim |> |> -- |> ========================================================================== |> / Achim Reiners Software Engineering \\ |> | /M/A/I Deutschland GmbH | |> | Softwarezentrum Koeln Phone: +49 221 956400-40 | |> | Mathias-Brueggen-Str. 85 Fax: +49 221 956400-69 | |> \\ 50829 Koeln E-Mail: ar@mai.de / |> \\_________________________________________________________________________/ This is correct as it now works. In complex query situations, you may need to be able to refer to the tables and columns multiple times. i.e. SELECT p.col1 prime_value, a.dscr value1_desc, b.dscr value2_desc FROM prime_table p, table_of_tables a, table_of_tables b WHERE p.value1 = a.tab_value AND p.value2 = b.tab_value is one example. That is why column and table aliasing (labeling) are there. For the order by you can say: ORDER BY p.col1, b.dscr, a.dscr -OR- ORDER BY 1,3,2 In the second case, the problem of reordering or additions to the SELECT clause may necessitate modification of the ORDER BY clause. DCT "I want my own monster. Get me a monster now!"