On Fri, Feb 7, 2020 at 3:23 PM Thorsten Schöning <[email protected]>
wrote:
> > No, but it makes working with them easier, as they now behave like
> ordinary
> > tables / views.
>
> I still don't get it: There's no place where I can interchange CTEs
> and my custom tables, only reuse individual components.
In your CteUsage.java file, the version from Jan 31, 2020, 7:06 PM, instead
of doing this:
CommonTableExpression<Record8<Integer, String, String, Long,
OffsetDateTime, MeterMfctCode, String, MeterType>> recsInTimePeriod =
this.cteRecsInTimePeriod();
and then this:
recsInTimePeriod.field(CteDefs.RecsInTimePeriod.COL_METER_MFCT_CODE),
You would have a globally available variable RECS_IN_TIME_PERIOD (ffrom
your RecsInTimePeriod CustomTable), and you could type safely dereference
columns from it, e.g. RECS_IN_TIME_PERIOD.COL_METER_MFCT_CODE
I.e.
private CommonTableExpression<Record7<MeterMfctCode, String,
MeterType, String, String, Integer, OffsetDateTime>>
cteReAndCltPerMeterLid()
{
CommonTableExpression<Record8<Integer, String, String, Long,
OffsetDateTime, MeterMfctCode, String, MeterType>> recsInTimePeriod =
this.cteRecsInTimePeriod();
// Still need to do some trickery when declaring the CTE
return CteDefs.ReAndCltPerMeterLid.CTE
.fields
(
CteDefs.RE_AND_CLT_PER_METER_LID.COL_METER_MFCT_CODE.getName(),
CteDefs.RE_AND_CLT_PER_METER_LID.COL_METER_READING_SERIAL.getName(),
CteDefs.RE_AND_CLT_PER_METER_LID.COL_METER_TYPE.getName(),
CteDefs.RE_AND_CLT_PER_METER_LID.COL_REAL_ESTATE_NR.getName(),
CteDefs.RE_AND_CLT_PER_METER_LID.COL_CLT_MAC.getName(),
CteDefs.RE_AND_CLT_PER_METER_LID.COL_METER_LID_CNT.getName(),
CteDefs.RE_AND_CLT_PER_METER_LID.COL_METER_LID_LAST_CAPTURED_AT.getName()
)
.as
(
// But when using the CTE, it looks like any ordinary SQL
DSL.select
(
RECS_IN_TIME_PERIOD.COL_METER_MFCT_CODE,
RECS_IN_TIME_PERIOD.COL_METER_READING_SERIAL,
RECS_IN_TIME_PERIOD.COL_METER_TYPE,
RECS_IN_TIME_PERIOD.COL_REAL_ESTATE_NR,
RECS_IN_TIME_PERIOD.COL_CLT_MAC,
DSL.count().as(RE_AND_CLT_PER_METER_LID.COL_METER_LID_CNT),
DSL
.max(RECS_IN_TIME_PERIOD.COL_CLT_REC_CAPTURED_AT))
.as(RE_AND_CLT_PER_METER_LID.COL_METER_LID_LAST_CAPTURED_AT)
)
.from(RECS_IN_TIME_PERIOD)
.groupBy
(
RECS_IN_TIME_PERIOD.COL_METER_MFCT_CODE),
RECS_IN_TIME_PERIOD.COL_METER_READING_SERIAL),
RECS_IN_TIME_PERIOD.COL_METER_TYPE),
RECS_IN_TIME_PERIOD.COL_REAL_ESTATE_NR),
RECS_IN_TIME_PERIOD.COL_CLT_MAC)
)
);
}
Does this make sense?
Or am I understanding your suggestion wrongly and I need to really
> implement CTE-SQL on my custom tables somehow? Like is done in the
> docs for custom fields, but I can't override "accept".
>
>
> https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-queryparts/
No, forget accept(). That's when you want to be in control of the generated
SQL, mostly useful with CustomField. Imagine DSL.field("abc"), but with the
capability of switching over the SQLDialect to produce dialect specific SQL
strings.
--
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].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/CAB4ELO6nJbrx4Uqh56F8nyYhmQwHrp%2BV4JS3V37rxtARadhXvw%40mail.gmail.com.
