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.
