Joe Wilson schrieb:
--- Trevor Talbot <[EMAIL PROTECTED]> wrote:
On 12/10/07, Robert Wishlaw <[EMAIL PROTECTED]> wrote:
IBM DB2 9.5
select a AS "foo" from t1 union select b from t1 order by foo
SQL0206N "FOO" is not valid in the context where it is used. SQLSTATE=42703
The problem here is with the inconsistent quoting. PostgreSQL uses the
opposite case folding as everyone else, hence the behavior difference.
Could someone please post the results of these queries on Oracle,
DB2 or SQL Server? If they don't work as is, could you show the syntax
(double quoted aliases or otherwise) that would make these queries work
on these particular databases?
Informix 10:
> select a, a+b AS c from t1 order by c;
a c
2 -998
1 3
> select a AS foo from t1 union select b from t1 order by foo;
foo
-1000
1
2
> select a, a+b AS c from t1 UNION ALL select a AS c, c AS a from t1 order by
c;
a c
2 -998
1 3
1 4
2 5
MS SQL Server 2005
select a, a+b AS c from t1 order by c;
a c
2 -998
1 3
select a AS foo from t1 union select b from t1 order by foo;
foo
-1000
1
2
select a, a+b AS c from t1 UNION ALL select a AS c, c AS a from t1 order by c;
a c
2 -998
1 3
1 4
2 5
Oracle 10g
SQL>
SQL> -- See if select alias or table column has precedence in ORDER BY
SQL> select a, a+b AS c from t1 order by c;
A C
---------- ----------
2 -998
1 3
SQL>
SQL> -- See if aliases accepted in UNION/ORDER BY combination
SQL> select a AS foo from t1 union select b from t1 order by foo;
FOO
----------
-1000
1
2
SQL>
SQL> -- See if ambiguous column aliases, UNIONs and ORDER BY work together
SQL> select a, a+b AS c from t1 UNION ALL select a AS c, c AS a from t1 order
by c;
A C
---------- ----------
2 -998
1 3
1 4
2 5
Michael
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
