Hi all, I have multiple different plain SQL-files using Common Table Expressions and need to migrate those to Java. Background is automatic reporting which gets enhanced in one of our software and it's most likely that lots of reports get added the same way I'm trying to do now. So things should be used as templates as much as possible.
CTEs are query-level and therefore not part of the classes jOOQ generates based on some schema for Java, that's why I wonder how to best handle names of CTEs themself, the columns they produce etc. In SQL-files, everything is simply text and in case of changed names, one uses search&replace of the editor of choice most likely. With Java OTOH, one could define classes and variables containing names to only maintain those at one place. Pretty much what jOOQ is doing when generating classes for tables etc., only that I need to do it myself. But is that worth it compared to simply using strings in Java as well? Search&Replace within files/classes isn't more difficult than in SQL-files. The official docs for CTEs in jOOQ simply use strings for names as well: https://www.jooq.org/doc/3.12/manual/sql-building/sql-statements/with-clause/ Would be great if people would simply tell how they deal with those names in their software. I've additionally attached the result of converting two CTEs I need into Java and what came into my mind to not need to duplicate names and stuff. But I'm unsure if it's worth it... Thanks for your opinions! Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning E-Mail: [email protected] AM-SoFT IT-Systeme http://www.AM-SoFT.de/ Telefon...........05151- 9468- 55 Fax...............05151- 9468- 88 Mobil..............0178-8 9468- 04 AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow -- 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/201259954.20200131131506%40am-soft.de.
WITH time_period AS
(
SELECT t.start_at,
t.end_at
FROM ( VALUES( (date_trunc('month', now()) - interval '1
month'),
(date_trunc('month', now()) -
interval '0 month'))
) AS t ( start_at, end_at )
),
recs_in_time_period AS
(
SELECT reading_company.user_id AS rc_user_id,
real_estate.number AS real_estate_nr,
collector.mac_address AS clt_mac,
clt_rec.id AS clt_rec_id,
clt_rec.captured_at AS clt_rec_captured_at,
meter.mfct_code AS meter_mfct_code,
meter.reading_serial AS meter_reading_serial,
meter.type AS meter_type
FROM time_period,
clt_rec
JOIN clt_rec_src ON clt_rec_src.clt_rec
= clt_rec.id
JOIN collector ON clt_rec_src.collector
= collector.id
JOIN real_estate ON collector.real_estate
= real_estate.id
JOIN reading_company ON real_estate.reading_company =
reading_company.id
JOIN oms_rec ON clt_rec.oms_rec
= oms_rec.id
JOIN meter ON oms_rec.meter
= meter.id
WHERE (clt_rec.captured_at BETWEEN time_period.start_at AND
time_period.end_at) AND
(real_estate.deleted IS FALSE)
AND
(meter.replaced_with IS NULL)
),
ctes.java
Description: Binary data
