JOOQ - error with alias and quotation marks

Question

I have this query:

Field<String> yearMonth = DSL.field("FORMATDATETIME({0}, 'yyyy-MM')",
                    String.class, LICENZE.CREATION_DATE).as("anno_mese");

List<Record3<Integer, String, String>> records = 
    create.select(DSL.count().as("num_licenze"), LICENZE.EDIZIONE, yearMonth).
    from(LICENZE).
    groupBy(LICENZE.EDIZIONE, yearMonth).
    orderBy(yearMonth).
    fetch();

This request generates:

select 
  count(*) "num_licenze", 
  "PUBLIC"."LICENZE"."EDIZIONE", 
  FORMATDATETIME("PUBLIC"."LICENZE"."CREATION_DATE", 'yyyy-MM') "anno_mese"
from "PUBLIC"."LICENZE"
group by 
  "PUBLIC"."LICENZE"."EDIZIONE", 
  "anno_mese"
order by "anno_mese" asc

executing it, I get: Column "anno_mese" not found; SQL statement

Testing the generated request and removing the quotes from anno_mesein each part of the request makes the request work.

Is my request wrong or am I using jooq wrong?

The alias in this request is not so important, I can run the request without using it, but just to understand how it works. I use h2 as a database.

thanks for the help

Answer 1

, H2, , . , jOOQ:

"anno_mese"

SQL , jOOQ. as("anno_mese") SELECT. GROUP BY ORDER BY.

Field<String> yearMonth = DSL.field("FORMATDATETIME({0}, 'yyyy-MM')",
                    String.class, LICENZE.CREATION_DATE);

List<Record3<Integer, String, String>> records = 
    create.select(DSL.count().as("num_licenze"), 
                  LICENZE.EDIZIONE, 
                  yearMonth.as("anno_mese")).
    from(LICENZE).
    groupBy(LICENZE.EDIZIONE, yearMonth).
    orderBy(yearMonth).
    fetch();

, jOOQ

jOOQ Settings, schema/table/ . :

DSLContext create = DSL.using(connection, SQLDialect.H2, 
    new Settings().withRenderNameStyle(RenderNameStyle.AS_IS);

, , : DSL.field(...).as("ANNO_MESE")