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

Source: Table1
order_number
line_item
quantity
unit_price
option

Target: Aggregate_Table_1

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.

Thanks.
daledog9Asked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production 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
)
0
 
slightwv (䄆 Netminder) Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
slightwv (䄆 Netminder) Commented:
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.  
0
 
slightwv (䄆 Netminder) Commented:
daledog9,

Can I ask why I didn't get partial credit?

I gave you everything except I missed "from (".  A pretty simple typo correction nav just beat me to it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.