I'm trying to "flatten" aggregate data and I'm having a hard time doing it in LLBL using predicate/aggregate clauses.

What I would like to do is show a list of accounts, with columns for each transaction type and the sum of that type of transaction. Normally I use a case statement for this (pseudocode):

SELECT
SUM(CASE WHEN transaction_type_id = 1 THEN amount ELSE 0 END) AS TYPE1 ,
SUM(CASE WHEN transaction_type_id = 2 THEN amount ELSE 0 END) AS TYPE2

etc.

I'm not even sure if this is a good practice (or good SQL) to begin with.

Anyway, is there a way of accomplishing this in LLBL? Would I use a compare set predicate? Should I just do it in a stored proc?

Thanks for any insight.

Phil

CASE statements are currently not supported. It might be the query can be re-written using different statements, though I have the feeling it will likely take a couple of queries to achieve hte same thing, as you use the CASE statement as a sort of WHERE filter.