--- 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?
create table t1(a INT, b INT, c INT);
insert into t1 values(1, 2, 4);
insert into t1 values(2, -1000, 5);
-- See if select alias or table column has precedence in ORDER BY
select a, a+b AS c from t1 order by c;
-- See if aliases accepted in UNION/ORDER BY combination
select a AS foo from t1 union select b from t1 order by foo;
-- See if ambiguous column aliases, UNIONs and ORDER BY work together
select a, a+b AS c from t1 UNION ALL select a AS c, c AS a from t1 order by c;
MySQL 5.0.45:
mysql> select a, a+b AS c from t1 order by c;
+------+------+
| a | c |
+------+------+
| 2 | -998 |
| 1 | 3 |
+------+------+
mysql> select a AS foo from t1 union select b from t1 order by foo;
+-------+
| foo |
+-------+
| -1000 |
| 1 |
| 2 |
+-------+
mysql> 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 |
+------+------+
PostgreSQL 8.1.5:
postgres=> select a, a+b AS c from t1 order by c;
a | c
---+------
2 | -998
1 | 3
postgres=> select a AS foo from t1 union select b from t1 order by foo;
foo
-------
-1000
1
2
postgres=> 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
____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
