thanks for the quick response... I guess I would be happy with any solution that would achieve the same results as the sample query I posted.
So MAX(CASE...END) is fine too... I just couldn't figure out how to do it. Sorry... if it's easy and you have time to post how that would be great. If not, this is just a one off for my system so I was going to just use a prepared statement. I did read the decode part of the documentation, but confess I got lost... thanks again, John On Friday, June 21, 2013 1:50:05 PM UTC-5, Lukas Eder wrote: > > 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] <javascript:>> > >> 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] <javascript:>. >> 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.
