Hi John, I'm not sure if I understand your question. Is it about transposing in general? Or about writing a MAX(CASE ... END) expression?
The CASE expression is indeed modeled by DSL.decode(), as you can see here: http://www.jooq.org/doc/3.0/manual/sql-building/column-expressions/case-expressions/ case (lower-cased) is a reserved word in Java, unfortunately, which is why I used DECODE() an Oracle function that has a similar purpose. Cheers Lukas 2013/6/21 John Childress <[email protected]> > I'm trying to transpose my data.... I was trying to figure out if decode > is what I need... I was trying to use a SelectQuery. > > My sql that "works" is here: > > SELECT > POINTID, > PERIODSTARTDATE, > PEAKTYPE, > max(CASE WHEN pricecomponent = 'A' THEN value END) AS DALMP, > max(CASE WHEN pricecomponent = 'B' THEN value END) AS DACONG, > max(CASE WHEN pricecomponent = 'C' THEN value END) AS DALOSS, > max(CASE WHEN pricecomponent = 'D' THEN value END) AS RTLMP, > max(CASE WHEN pricecomponent = 'E' THEN value END) AS RTCONG, > max(CASE WHEN pricecomponent = 'F' THEN value END) AS RTLOSS, > max(CASE WHEN pricecomponent = 'G' THEN value END) AS HALMP, > max(CASE WHEN pricecomponent = 'H' THEN value END) AS HACONG, > max(CASE WHEN pricecomponent = 'I' THEN value END) AS HALOSS, > max(CASE WHEN pricecomponent = 'J' THEN value END) AS MCP, > max(CASE WHEN pricecomponent = 'K' THEN value END) AS DART_RETURN, > COUNT > FROM PRICE_TABLE > WHERE (PERIODSTARTDATE >= DATE '2013-01-01' AND PERIODSTARTDATE <= DATE > '2013-05-17' AND POINTID = 12345 AND PEAKTYPE = 'OFFPEAK') > > Am I missing it in the docs? > > thanks, > > John > > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
