Strange QueryDsl {alias.*}

Question

In my sql I also want to group by an expression that is calculated.

I came up with a solution inspired by https://groups.google.com/forum/#!topic/querydsl/z-uIRU5jVcM.

The sql I wanted to generate structurally looks like this, where I perform the calculation - a mapping - with the inner sql, then join some other fields and do the group by with the join:

select
col1,
...
calculatedExpressionAlias,
count(col1) 
from t
left join
(
   select
   ro1.ID,
   (
      case when .. then ..
      ...
      else  
      end
   )
   as calculatedExpressionAlias
   from t
)
as s on s.ID = t.ID
group by col1,
...
calculatedExpressionAlias

This sql should be generated by QueryDsl from a JPASQLQuery.

It almost looks like this, but the remaining problem is a strange alias inside the generated sql. The alias I associate with the calculated case expression (for instance "calculatedExpressionAlias"), appears with curly braces and star in the projection {.*}

select
...
{calculatedExpressionAlias.*},
...
left join
(
select
...
(
  case when .. then ..
) 
as calculatedExpressionAlias
...
group by 
...
calculatedExpressionAlias

What is the reason for this and how could it be fixed?

Here is the original JPASqlQuery, apologies it's a bit longer:

    SRoRohrnetzobjekt ro1 = new SRoRohrnetzobjekt("ro1");
    SRoRohrnetzobjekt ro2 = new SRoRohrnetzobjekt("ro2");
    SRoRohrnetzobjekt ro3 = new SRoRohrnetzobjekt("ro3");
    SSRbstelle rb = new SSRbstelle("rb");

    DateTimePath<Timestamp> naechsteInspektion = ro1.kontrollzyklusNaechsteinspektion;


    NumberExpression<Integer> dringlichkeit =

            Expressions
                    .cases()

                    .when(p.imIntervall(naechsteInspektion,
                            InspektionsDringlichkeit.ROHRNETZ_INSPEKTION_DRINGLICHKEIT_SOFORT))
                    .then(InspektionsDringlichkeit.ROHRNETZ_INSPEKTION_DRINGLICHKEIT_SOFORT.getStatusWert())

                    .when(p.imIntervall(naechsteInspektion,
                            InspektionsDringlichkeit.ROHRNETZ_INSPEKTION_DRINGLICHKEIT_4_WOCHEN))
                    .then(InspektionsDringlichkeit.ROHRNETZ_INSPEKTION_DRINGLICHKEIT_4_WOCHEN.getStatusWert())

                    .when(p.imIntervall(naechsteInspektion,
                            InspektionsDringlichkeit.ROHRNETZ_INSPEKTION_DRINGLICHKEIT_12_WOCHEN))
                    .then(InspektionsDringlichkeit.ROHRNETZ_INSPEKTION_DRINGLICHKEIT_12_WOCHEN.getStatusWert())

                    .when(p.imIntervall(naechsteInspektion,
                            InspektionsDringlichkeit.ROHRNETZ_INSPEKTION_KEINE_DRINGLICHKEIT))
                    .then(InspektionsDringlichkeit.ROHRNETZ_INSPEKTION_KEINE_DRINGLICHKEIT.getStatusWert())

                    .otherwise(InspektionsDringlichkeit.ROHRNETZ_INSPEKTION_KEINE_DRINGLICHKEIT.getStatusWert());

    Path<Integer> d = new PathBuilder<Integer>(Integer.class, "dringlichkeit");

    ListSubQuery<Tuple> sub = new SQLSubQuery()
            .from(ro1)
            .list(ro1.id, dringlichkeit.as(d));

    return new JPASQLQuery(entityManager, new Db2SqlTemplates())
            .from(ro2)
            .leftJoin(rb).on(ro2.rbstelleId.eq(rb.id))
            .leftJoin(sub, ro3)
            .on(ro3.id.eq(ro2.id))
            .groupBy(
                    rb.bezeichnung,
                    ro2.unterbezeichnung,
                    ro2.adresseOrtsteil,
                    ro2.adressePlz,
                    d)
            .list(
                    new QFaelligeGruppe(
                            rb.bezeichnung,
                            ro2.unterbezeichnung,
                            ro2.adressePlz,
                            ro2.adresseOrtsteil,
                            d,
                            ro2.count()));

Solution is: Path<Integer> d = Expressions.numberPath(Integer.class, "dringlichkeit"); There is still another problem (at least for DB2: parameter usage is not allowed where I use it. — Alex
Problem is possibly that DB2 does not allow untyped parameter markers for case results and QueryDsl with my config at least doesn't produce typed markers - with a (Cast ? as Integer) for instance — Alex

Alex Alex · Accepted Answer · 2015-07-31T13:03:24

Solution is to construct the alias with the Expressions factory:

Path<Integer> d = Expressions.numberPath(Integer.class, "dringlichkeit");

Strange QueryDsl {alias.*}

1 Answers