[SQL] JPQL how to count distinct values on a union between two tables?

@Query("SELECT v.nome AS nome, v.documento AS documento, " +
"COUNT(DISTINCT CASE WHEN f.fluxoStatus = 'CONVERSAO' THEN f.fluxoId END) AS totalConvertido, " +
"COUNT(DISTINCT CASE WHEN f.fluxoStatus = 'PROCESSANDO' THEN f.fluxoId END) AS totalEmProcesso, " +
"COUNT(DISTINCT CASE WHEN f.fluxoStatus = 'PERDIDO' THEN f.fluxoId END) AS totalPerdido, " +
"COUNT(DISTINCT CASE WHEN r.source = 'ANIVERSARIANTES' THEN r.recordId END) AS aniversariantesContatados, " +
"COUNT(DISTINCT CASE WHEN r.source = 'INATIVOS' THEN r.recordId END) AS inativosContatados, " +
"COUNT(DISTINCT disparo.id) AS totalDisparos, " +
"COUNT(DISTINCT disparoRecord.recordId) AS clientesDisparados," +
"COUNT(uniao.voId) AS conversoesDigitais " +
"FROM Vendedor v " +
"LEFT JOIN v.fluxos f ON f.fluxoMoment BETWEEN :inicio AND :fim " +
"LEFT JOIN Record r ON r.userAuthor = v.user AND r.moment BETWEEN :inicio AND :fim " +
"LEFT JOIN DisparadorEntidade disparo ON disparo.author = v.user AND disparo.startedAt BETWEEN :inicio AND :fim " +
"LEFT JOIN DisparoRecordsEntidade disparoRecord ON disparoRecord.disparo=disparo " +
"LEFT JOIN (SELECT DISTINCT uniao0.voId AS voId FROM (SELECT x1.voId AS voId FROM disparoRecord.pedido x1 UNION ALL SELECT x2.voId AS voId FROM r.pedido x2) uniao0) AS uniao " +
"GROUP BY v.nome,v.documento " +
"ORDER BY v.documento ")
List<VendorsProjection> getVendorsSummary(LocalDateTime inicio, LocalDateTime fim);


This query works if remove "LEFT JOIN (SELECT DISTINCT uniao0.voId AS voId FROM (SELECT x1.voId AS voId FROM disparoRecord.pedido x1 UNION ALL SELECT x2.voId AS voId FROM r.pedido x2) uniao0) AS uniao " AND COUNT(uniao.voId) this part which is supposed to count voId on an unique way on the two tables union but I'm getting:

[Unknown column 'd2_0.vo_pedido_id' in 'where clause'] [n/a]; SQL [n/a]


Even this column surely existing on d2_0, I can't tell how to achieve this I think jpql is dommed to get bugged on complex queries

Continue reading...