[ 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
