Analytics Software

Not sure why I am not getting how to aggregate in ODI! I have been trying to come up with a way to produce these results in ODI for 2 weeks now, and I am missing something. I don't believe this should be that difficult. This is the sql code I am using in sqlplus to yield test results and here is my scenerio:


sql:

select project_code
from
(select project_code, floor(sum(unit_price*quantity)) as total
from table1
where order_base_number=8004
group by project_code)
where total =
(select max(floor(sum(unit_price*quantity)))
from table1
where po_header_id = 8004
group by project_code)
and rownum = 1;




Source: Table1 contain fields:

order_base_number
order_mod_number
line_item
quantity
unit_price
project_code

Target: Aggregate_Table_1 contains

order_base_number
order_mod_number
aggregate_project_code = the project_code should be the project code with the highest total_cost grouped by order_base_number,order_mod_number (if the result returns = value then return 1st occurence rn=1)

for example,

order - 1001
mod_number - 200
line_item - 1
quantity - 100
unit_price - 100
project_code - 55001

order - 1001
mod_number - 200
line_item - 2
quantity - 100
unit_price - 10
project_code - 55002

order - 1001
mod_number - 200
line_item - 3
quantity - 100
unit_price - 5
project_code - 55001

Results

order_base_number - 1001
order_mod_number - 200
aggregate_project_code - 55001