So I'm proceeding with the following for now:
Things a = THINGS.as("a");
Field<Date> truncDay = Factory.field("trunc({0}, {1})", SQLDataType.DATE,
a.ENTRY_DATE, inline("DD")).as("day");
Which renders on HSQL
trunc("a"."ENTRY_DATE", 'DD') as "day"
Which is cool, but I was wondering if there is way to get rid of the quotes
around day, i.e.
trunc("a"."ENTRY_DATE", 'DD') as day
?
On Fri, Nov 23, 2012 at 12:48 PM, Lukas Eder <[email protected]> wrote:
> According to the Javadocs of field(String, Class, QueryPart...), you
> should only supply QueryPart objects to the plain SQL field:
>
> http://www.jooq.org/javadoc/latest/org/jooq/impl/Factory.html#field(java.lang.String
> ,
> java.lang.Class, org.jooq.QueryPart...)
>
> If you pass a org.jooq.DatePart object, the compiler will infer this
> method, instead:
>
> http://www.jooq.org/javadoc/latest/org/jooq/impl/Factory.html#field(java.lang.String
> ,
> java.lang.Class, java.lang.Object...)
>
> Where Object... is an array of bind values. You have several options,
> for instance:
> - Factory.field("trunc({0}, day)", ...);
> - Factory.field("trunc({0}, {1}", ..., Factory.inline("day"));
>
> Or, again, you can create a CustomField to correctly handle
> dialect-specific behaviour of the TRUNC function:
>
> http://www.jooq.org/doc/2.6/manual/sql-building/queryparts/custom-queryparts/
>
> Cheers
> Lukas
>
> 2012/11/23 Ben Hood <[email protected]>:
> > Following this example, if I attempt the following:
> >
> > Field<Date> day = Factory.field("trunc({0}, {1})", SQLDataType.DATE,
> > THINGS.ENTRY_DATE, DatePart.DAY);
> >
> > I get the following exception:
> >
> > org.jooq.exception.SQLDialectNotSupportedException: Type class
> > org.jooq.DatePart is not supported in dialect null
> > at org.jooq.impl.AbstractDataType.getDataType(AbstractDataType.java:478)
> > at org.jooq.impl.FieldTypeHelper.getDataType(FieldTypeHelper.java:972)
> > at org.jooq.impl.Factory.getDataType(Factory.java:6245)
> > at org.jooq.impl.Util.queryParts(Util.java:533)
> > at org.jooq.impl.SQLField.<init>(SQLField.java:59)
> > at org.jooq.impl.Factory.field(Factory.java:1291)
> >
> > I also tried this with Factory instance method, i.e.
> >
> > Factory db = new Factory(ds, SQLDialect.HSQLDB);
> > Field<Date> day = db.field("trunc({0}, {1})", SQLDataType.DATE,
> > THINGS.ENTRY_DATE, DatePart.DAY);
> >
> > and I got the same error.
> >
> > Is there a better way to supply the dialect?
> >
> > On Fri, Nov 23, 2012 at 12:20 PM, Lukas Eder <[email protected]>
> wrote:
> >>
> >> Factory.field("trunc({0}, {1})", SQLDataType.TIMESTAMP, date, datepart);
> >
> >
>
