Filter Aggregate Data in Oracle Data Integrator 11g

I am having difficulting figuring out Oracle Data Integrator and how to filter an aggregate column,

for example - I am taking data from a source and aggregating it to a target. Within one of the target fields I would like to filter - here is the scenerio

total_cost = sum(quantity*unit_price)
total_cost_less_options = sum(quantity*unit_price) where option='N'

I am having difficulting for the mapping for total_cost_less_options in ODI interface, when I create the implementation I am not getting the syntax correct.

are you aggregating across the entire table or for orders?

updating/inserting int the aggregate table should be straight forward:

select order_number,
sum(options) over(partition by order_number order by order_number) options_price,
sum(unit_price) over(partition by order_number order by order_number) total_price
select order_number, case when option='N' then 0 else unit_price end options, unit_price
from table1
)

0

daledog9Author Commented:

I agree, the sql is very straight forward - but I cannot figure out how to use the expression builder in ODI to get my results. When I run straight SQL I get my desired results, but I keep getting errors when writing the same code the expression builder within ODI.

0

daledog9Author Commented:

to further explain my note above ...
In the mapping view of the Interface for the fields -

Implementation for total_cost field = sum(quantity*unit_price)
No errors

Implementation for total_cost_less_options field = sum(quantity*unit_price) where option='N'
Receive error: java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected

I found under quick edit tab where you can create a filter, but I do not want the entire dataset filtered, just the calculated field of total_cost_less_options

0

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

I apologize. I didn't see the ODI reference when I first read the question.

I've never used the product myself so this is a huge guess but try something like:

total_cost field = sum(case when option = 'N' then quantity*unit_price else 0 end)

0

daledog9Author Commented:

Thank you for adding the additional zones. The syntax you suggested pass the check, but now it's failing during executing with ORA-00920: invalid relational operator. It does not seem to be processing the group by properly as it performs that automatically- or rather I am not understanding how ODI works in respect to group by.

Naveen KumarProduction Manager / Application Support ManagerCommented:

i think a typo here.

select order_number,
sum(options) over(partition by order_number order by order_number) options_price,
sum(unit_price) over(partition by order_number order by order_number) total_price
select order_number, case when option='N' then 0 else unit_price end options, unit_price
from table1
)

it should be the below right ?

select order_number,
sum(options) over(partition by order_number order by order_number) options_price,
sum(unit_price) over(partition by order_number order by order_number) total_price
from (
select order_number, case when option='N' then 0 else unit_price end options, unit_price
from table1
)

updating/inserting int the aggregate table should be straight forward:

select order_number,

sum(options) over(partition by order_number order by order_number) options_price,

sum(unit_price) over(partition by order_number order by order_number) total_price

select order_number, case when option='N' then 0 else unit_price end options, unit_price

from table1

)