Bug 781092 (SOA-3582)

Help Desk Ticket Reference: https://c.na7.visual.force.com/apex/Case_View?id=500A0000008ULPj
project_key: SOA

suggestion for a potential optimization: It concerns federated queries which do expressions. This is an extremely common requirement in the banking business, the main example being when monetary amounts need to be converted into a reporting currency using a rate in another database.

With the query below, it makes sense that despite the group by, EDS will pull back all the rows so it can multiply one by the other. Unfortunately this is often many millions of records.

Would it be feasible to have an optimization which push-down most of the aggregation to the source, then do a further aggregation in EDS? 

 * sum the tsl.func_amount, grouped by tsl.currency
 * next: get the conversion rates for all the currency codes which have been returned, then sum(total * rate)

Of course, this can only be done for certain kinds of expressions.

select sum(tsl.func_amount * ccy.EuroConversionLegacyRate)
  from TSL_FINANCIAL_ACCOUNT_ITEMS_T2 tsl
  join t_sdm_currency ccy on tsl.currency = ccy.currencycode
 where tsl.business_date = '2011-06-29'
 group by ccy.EuroConversionLegacyRate