Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3407
  • Last Modified:

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.
0
daledog9
Asked:
daledog9
  • 3
  • 3
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

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!

 
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
 
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
)
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now