2

I want to setup a SQL case expression via the JPASQLQuery API as follows:
The Oracle SQL is:

    SELECT h.ordercode
  , h.requesteddeliverytime
  , CASE
        WHEN h.ordergroup IS NOT NULL
        THEN g.minRequestedDeliveryTime
        ELSE h.requesteddeliverytime
    END AS earliest_del_time
   FROM orderheader h LEFT JOIN (
                           SELECT 
                            ordergroup
                           ,MIN(requesteddeliverytime) as minRequestedDeliveryTime
                          FROM orderheader hh
                         GROUP BY orderGroup   
                 ) g ON h.ordergroup = g.ordergroup

and delivers the correct results. When I try to setup a CaseBuilder(), I get back a simple Expression, which then can't be used in a JPASQLQuery anymore (at least I'm not able to set an alias with .as("someString")).

The metadata class looks as follows (short form):

    @Generated("com.mysema.query.sql.codegen.MetaDataSerializer")
public class SOrderheader extends com.mysema.query.sql.RelationalPathBase<SOrderheader> {

    public final StringPath ordergroup = createString("ORDERGROUP");
    public final DateTimePath<java.sql.Timestamp> requesteddeliverytime = createDateTime("REQUESTEDDELIVERYTIME", java.sql.Timestamp.class);
}

and the group query looks as follows:

    ListSubQuery<Tuple> groupSubQuery = new SQLSubQuery()
        .from(sOrderGroup)
        .groupBy(sOrderGroup.ordergroup)
        .list(new QTuple(
                sOrderGroup.ordergroup.as("orderGroup"),
                sOrderGroup.id.count().as("nofOrdersPerGroup"),
                sOrderGroup.requesteddeliverytime.min().as("earliestDeliveryTime"),
                ));

CaseBuilder() statement:

        Expression<Timestamp> earliestDeliveryTime = new CaseBuilder() 
            .when(sOrderHeader.ordergroup.isNotNull()).then(og.getDate("earliestDeliveryTime", Date.class)) 
            .otherwise(sOrderHeader.requesteddeliverytime);

I'd like to have some means to return different columns in the various CASE alternatives and / or the possibility to set aliases on the expressions returned from the CaseBuilder(). Any help is appreciated.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
M. Lindenmann
  • 81
  • 1
  • 2
  • 6

0 Answers0