Incompatible type does not allow for union of subqueries in JOOQ dsl

Question

I am trying to use JOOQ to build the following query in java programmatically:

 select emisor,
       anio,
       mes,
       sum(case when codigo = '01' then total else 0 end) as facturas,
       sum(case when codigo = '03' then total else 0 end) as boletas,
       sum(case when codigo = '07' then total else 0 end) as notas_credito,
       sum(case when codigo = '08' then total else 0 end) as notas_debito,
       sum(case when codigo = 'RC' then total else 0 end) as resumenes,
       sum(case when codigo = 'RA' then total else 0 end) as anulaciones,
       sum(case when codigo = '40' then total else 0 end) as percepciones,
       sum(case when codigo = '20' then total else 0 end) as retenciones,
       sum(case when codigo = 'RV' then total else 0 end) as reversiones,
       sum(case when codigo = '09' then total else 0 end) as guias
from (select ruc_emisor                      as emisor,
             year(fec_registro)              as anio,
             month(fec_registro)             as mes,
             substring(nom_solicitud, 13, 2) as codigo,
             count(*)                        as total
      from bd_ose.tx_solicitud
      where year(fec_registro) = '2019'
        and month(fec_registro) = 7
      group by ruc_emisor, anio, mes, codigo
      UNION
      select num_ruc             as emisor,
             year(fec_registro)  as anio,
             month(fec_registro) as mes,
             cod_cpe             as codigo,
             count(*)            as total
      from bd_ose.tx_comprobante_inf
      where year(fec_registro) = '2019'
        and month(fec_registro) = 7
      group by num_ruc, anio, mes, codigo
     ) solicitudes
group by emisor, anio, mes
order by emisor;

I am still fairly inexperienced when it comes to SQL and JOOQ, but I decided to begin from the inside and work my way out. The problem I am having comes when I try to apply the .union() method to the two inner subqueries. I am getting a type missmatch error highlighted by my IDE pointing out that union expects a parameter of type "org.jooq.Select<..." while the one I am providing is of type "org.jooq.SelectHavingStep<..." - this is the type returned from .groupBy()

I have already checked the documentation on [union docs] (https://www.jooq.org/doc/3.11/manual/sql-building/sql-statements/select-statement/union-clause/) and tried looking for similar cases elsewhere but unfortunately havent been successful yet.

Here's what I have so far:

package pe.net.tci.osereporterservice;

import org.jooq.*;
import org.jooq.impl.DSL;
import org.springframework.stereotype.Repository;
import pe.net.tci.osereporterservice.jooq.tables.TxComprobanteInf;
import pe.net.tci.osereporterservice.jooq.tables.TxSolicitud;

import java.util.List;

@Repository
public class ReportDAO {

  private final DSLContext dslContext;

  TxComprobanteInf txComprobanteInf = TxComprobanteInf.TX_COMPROBANTE_INF;
  TxSolicitud txSolicitud = TxSolicitud.TX_SOLICITUD;

  public ReportDAO(DSLContext dslContext) {
    this.dslContext = dslContext;
  }

  public List<ReportEntry> produceReport() {

    Field<Integer> CIanio = DSL.year(txComprobanteInf.FEC_REGISTRO).as("anio");
    Field<Integer> CImes = DSL.month(txComprobanteInf.FEC_REGISTRO).as("mes");
    Field<?> CIemisor = txComprobanteInf.NUM_RUC.as("emisor");
    Field<?> CIcodigo = txComprobanteInf.COD_CPE.as("codigo");

    Field<Integer> Sanio = DSL.year(txSolicitud.FEC_REGISTRO).as("anio");
    Field<Integer> Smes = DSL.month(txSolicitud.FEC_REGISTRO).as("mes");
    Field<?> Semisor = dslContext.select(txSolicitud.RUC_EMISOR.as("emisor");
    Field<?> Scodigo = DSL.substring(txSolicitud.NOM_SOLICITUD, 13, 2).as("codigo");

 dslContext.select(
        Semisor,
        Sanio,
        Smes,
        Scodigo,
        DSL.count().as("total")
    ).from(txSolicitud)
        .where(
            (DSL.year(txComprobanteInf.FEC_REGISTRO).eq(2019))
                .and(DSL.month(txComprobanteInf.FEC_REGISTRO).eq(7))
        ).union(

  dslContext.select(
            CIemisor,
            CIanio,
            CImes,
            CIcodigo,
            DSL.count().as("total")
        ).from(txComprobanteInf)
            .where(
                (CIanio.eq(2019))
                    .and(CImes.eq(7))
            ).groupBy(CIemisor, CIanio, CImes, CIcodigo))
//...more code
  }

}

Any help translating this SQL into JOOQ or pointing out useful resources to do with this will be greatly appreciated.

Answer 1

The problem here is that you are declaring some variables as of type Field<?> and that the Java compiler as a result doesn't see the two Select objects as being compatible. So instead of:

Field<?> CIemisor = txComprobanteInf.NUM_RUC.as("emisor");
Field<?> CIcodigo = txComprobanteInf.COD_CPE.as("codigo");

you should declare the two variables using the appropriate generic type parameter. E.g.

Field<String> CIemisor = txComprobanteInf.NUM_RUC.as("emisor");
Field<String> CIcodigo = txComprobanteInf.COD_CPE.as("codigo");

The same goes for the other two variables. (I noticed that for the variable Semisor you need to get rid of the dslContext.select( part in the initializer. I assume this is related to a test you did.)

I think this change should resolve your problems.