Group by trunc(date)
Hi,
lets say I have a source table like this
Customer _ TimeStamp ________ BillAmount
001 ______ 01/01/2012 11:00 __ 100,00
001 ______ 01/01/2012 17:00 __ 150,00
I want to load a destination table like this
Customer _ Date ________ BillAmount
001 ______ 01/01/2012 __ 250,00
Pretty simple, using SQL this wuold be
select
Customer,
trunc(TimeStamp),
sum(BillAmount)
from source_table
group by
Customer,
trunc(TimeStamp)
But I cant get ODI to generate SQL code like this.
ODI generate code like the following
select
Customer,
trunc(TimeStamp),
TimeStamp
sum(BillAmount)
from source_table
group by
Customer,
trunc(TimeStamp),
TimeStamp
Looks like ODI find out no aggregation function is used on the TimeStamp column, so it believes it has to group by it.
Usually correct, but not this time.
The only way I can imagine to do this with ODI is not using ODI, ie using a pre-aggregated table as source.
Is there a better way?
Regards,
Corrado