OK, good to know. It would be nice to be able to control the alias
rendering by itself. FWIW I looked into the alias rendering works and it
appears to assume that for HSQL, aliases are rendered with double quotes.
This appears to be fine for normal queries but presents a problem with
subqueries since a quoted alias is not available to the outer query.
FTR, this is the query that I'm building (sorry about the noise with the
self-join in the inner query - this variant needs to run on HSQL which
doesn't support window functions):
Settings s = new Settings();
s.setRenderNameStyle(RenderNameStyle.AS_IS);
Factory db = new Factory(ds, SQLDialect.HSQLDB, s);
Things a = THINGS.as("a");
Things b = THINGS.as("b");
Field<Object> day = field("day");
Field<Object> month = field("month");
Field<Object> year = field("year");
Field<Object> bucket = field("bucket");
Field<Object> version = field("version");
Field<Object> id = field("id");
Field<Object> digest = field("digest");
Field<Date> truncDay = Factory.field("trunc({0}, {1})", SQLDataType.DATE,
a.ENTRY_DATE, inline("DD"));
Field<Date> truncMonth = Factory.field("trunc({0}, {1})", SQLDataType.DATE,
day, inline("MM"));
Field<Date> truncYear = Factory.field("trunc({0}, {1})", SQLDataType.DATE,
month, inline("YY"));
Field<Integer> bucketCount = Factory.cast(ceil(cast(count(),
SQLDataType.REAL).div(5)), SQLDataType.INTEGER);
SelectHavingStep slicedBuckets =
db.select(day, bucket, function("md5", String.class,
groupConcat(version).orderBy(id.asc()).separator("")).as(digest.getName())).
from(
db.select(truncDay.as(day.getName()), a.ID.as(id.getName()),
a.VERSION.as(version.getName()), bucketCount.as(bucket.getName())).
from(a).
join(b).
on(a.ID.eq(b.ID)).
and(a.ID.ge(b.ID)).
groupBy(truncDay, a.ID, a.VERSION).
orderBy(truncDay,bucket)
).
groupBy(day, bucket);
SelectHavingStep dailyBuckets =
db.select(day, function("md5", String.class,
groupConcat(digest).orderBy(bucket.asc()).separator("")).as(digest.getName())).
from(slicedBuckets).groupBy(day);
SelectHavingStep monthlyBuckets =
db.select(truncMonth.as(month.getName()), function("md5", String.class,
groupConcat(digest).orderBy(day.asc()).separator("")).as(digest.getName())).
from(dailyBuckets).groupBy(truncMonth);
SelectHavingStep yearlyBuckets =
db.select(truncYear.as(year.getName()), function("md5", String.class,
groupConcat(digest).orderBy(month.asc()).separator("")).as(digest.getName())).
from(monthlyBuckets).groupBy(truncYear);
Iterator<Record> it = yearlyBuckets.fetch().iterator();
On Sun, Nov 25, 2012 at 10:05 PM, Lukas Eder <[email protected]> wrote:
> In most cases, RenderNameStyle.AS_IS will work correctly for the SQL
> generated by jOOQ, except when:
>
> - SQL identifiers are case-sensitive
> - SQL identifiers contain special characters
>
> Cheers
> Lukas
>
> 2012/11/23 Ben Hood <[email protected]>:
> > I think I may have a workaround for my alias escaping issue with HSQLDB:
> >
> > Settings s = new Settings();
> > s.setRenderNameStyle(RenderNameStyle.AS_IS);
> > Factory db = new Factory(ds, SQLDialect.HSQLDB, s)
> >
> > I'm not sure whether this will have any other unwanted side effects
> though.
> >
> > On Fri, Nov 23, 2012 at 4:35 PM, Ben Hood <[email protected]> wrote:
> >>
> >> 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);
> >>> >
> >>> >
> >>
> >>
> >
>
