db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jack Klebanoff (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-84) Column aliasing could simplify queries
Date Thu, 02 Jun 2005 15:32:55 GMT
     [ http://issues.apache.org/jira/browse/DERBY-84?page=comments#action_66872 ]
     
Jack Klebanoff commented on DERBY-84:
-------------------------------------

I suspect that this is not a bug, that Derby is behaving correctly here according to the SQL
standard.

The alias in the select list names the column in the virtual table produced by the SELECT.
However Derby requires that where clause column references refer to columns in tables, views,
or sub-queries listed in the FROM clause. In the bug report example LDCN is not a column in
in any from list table, so Derby issues an error message when LDCN is used in the where clause.

My reading of the SQL2003 spec indicates that the Derby behavior follows the SQL standard.
See the standard's discussion of identifier chains. The spec differentiates between identifiers
used in ORDER BY clauses and identifiers used elsewhere. The spec says that (column) identifiers
in a ORDER BY clause should be bound to the column names defined in the select list, but that
other (column) identifiers should be bound to columns in tables in the FROM list.



> Column aliasing could simplify queries
> --------------------------------------
>
>          Key: DERBY-84
>          URL: http://issues.apache.org/jira/browse/DERBY-84
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.0
>     Reporter: Bob Gibson
>     Priority: Minor

>
> Currently, one can not use an alias to simplify queries.  For example, being able to
alias "LongDescriptiveColumnName" AS LDCN would allow one to use the alias elsewhere in the
query, e.g., the WHERE clause:
> SELECT LongDescriptiveColumnName AS LDCN FROM MyTable WHERE LDCN LIKE '%testing%';
> The current result is a message like:
> ERROR 42X04: Column 'LDCN' is not in any table in the FROM list or it appears within
a join specification and is outside the scope of the join specification or it appears in a
HAVING clause and is not in the GROUP BY list.  If this is a CREATE or ALTER TABLE statement
then 'LDCN' is not a column in the target table.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Mime
View raw message