Hello, >From how I understand your query, both the WORKPROGRESS table and your derived table DEPT_BY_DATE contain a DEPTCD column, which is why it is not correct to simply reference it using the "deptCd" column alias. Besides, I don't think you can declare an alias and immediately use it in the same SELECT clause, i.e. this is not possible:
SELECT 1 AS a, doSomethingWith(a) -- Can't use a here FROM dual A simple solution would be to do: Field<String> FIELD_DEPT_BY_DATE_DEPT_CD = DEPT_BY_DATE.field(Tables. WORK.DEPTCD); There is no need to alias this expression. Let me know if this helped. Cheers Lukas 2014-05-28 8:28 GMT+02:00 msr <[email protected]>: > Following java code throws an exception 'Column 'deptCd' in field list is > ambiguous'. > > Jooq: 3.3.2 > MySQL: 5.5.27 > > /*** SQL ***/ > SELECT WorkProgress.projectCd > WorkProgress.date, > WorkProgress.deptCd AS projectDeptCd, > DeptByDate.deptCd, > FX_GetParentDeptCd( > WorkProgress.date, > DeptByDate.deptCd /*** HERE ***/ > ) AS parentDeptCd > FROM WorkProgress > INNER JOIN ( > SELECT Work.workDate, > Work.deptCd > FROM Work > UNION > SELECT Travel.tripDate, > Travel.deptCd > FROM Travel > ) DeptByDate > on WorkProgress.date = DateByDate.date > > /*** java ***/ > Table<Record> DEPT_BY_DATE = create > .select( > Tables.WORK.WORKDATE, > Tables.WORK.DEPTCD > ) > .from(Tables.WORK) > .union( > create.select( > Tables.TRAVEL.TRIPDATE, > Tables.TRAVEL.DEPTCD > ) > .from(Tables.TRAVEL) > ).as("DeptByDate"); > > Field<String> FIELD_PROJECT_DEPT_CD = Tables.WORKPROGRESS.DEPTCD.as > ("projectDeptCd"); > Field<String> FIELD_DEPT_BY_DATE_DEPT_CD = > DEPT_BY_DATE.field(Tables.WORK.DEPTCD).as("deptCd"); > Field<String> FIELD_DEPT_CD = Routines.fxGetParentDeptCd( > Tables.WORKPROGRESS.DATE, > FIELD_DEPT_BY_DATE_DEPT_CD /*** HERE ***/ > ); > Result<Record> records = create > .select( > Tables.WORKPROGRESS.PROJECTCD, > Tables.WORKPROGRESS.DATE, > FIELD_PARENT_DEPT_CD, > FIELD_DEPT_BY_DATE_DEPT_CD, > FIELD_DEPT_CD > ) > .from(Tables.WORKPROGRESS) > .join(DEPT_BY_DATE) > > .on(Tables.WORKPROGRESS.WORKDATE.equal(DEPT_BY_DATE.field(Tables.WORK.WORKDATE)) > .fetch(); > > /*** exception ***/ > [select `WorkProgress`.`projectCd`, `WorkProgress`.`date`, > `WorkProgress`.`deptCd` as `projectDeptCd`, > FX_GetParentDeptd(`WorkProgress`.date, `deptCd`) as `parentDeptCd` > <--- HERE > from `WorkProgress` join (select .....)]; Column 'deptCd' in field list is > ambiguous > ... > at org.jooq.impl.Utils.translate(Utils.java:1289) > at > org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:495) > at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:327) > at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:330) > at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2256) > Caused by: > com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: > Column 'deptCd' in field list is ambiguous > ... > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
