db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Micah Spears (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-84) Column aliasing could simplify queries
Date Wed, 12 Jan 2005 22:39:17 GMT
     [ http://issues.apache.org/jira/browse/DERBY-84?page=comments#action_57566 ]
     
Micah Spears commented on DERBY-84:
-----------------------------------

This issue is hindering Hibernate support.  HQL queries using 'group by' and 'having' clauses
fail in cloudscape because hibernate uses column aliases.  An example is below.

HQL: select p.rxId from gov.va.med.predemo.local.pharmacy.db.pharmacy.hibernate.PatientPrescriptionDO
as p, p.prescriptionStatusLogs as s group by p having max(s.statusId) = :status

SQL: select patientpre0_.RX_ID as x0_0_ from Pharmacy.PATIENT_PRESCRIPTION patientpre0_ inner
join Pharmacy.PRESCRIPTION_STATUS_LOG prescripti1_ on patientpre0_.RX_ID=prescripti1_.RX_ID
group by  patientpre0_.RX_ID having (max(prescripti1_.STATUS_ID)=? ) 

Message: com.ibm.db2.jcc.c.SqlException: Column 'PATIENTPRE0_.X0_0_' 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 'PATIENTPRE0_.X0_0_' is not a column in the target
table.


If I remove 'patientpre0_.RX_ID as x0_0_', the query runs as expected.

> 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
-
If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira


Mime
View raw message