ODI 12c Implementing Complex queries ( Analytic Functions & SubSelect Query )

Hi

In this entry i will implement more advance query over  ODI 12c.Again I am gonna use ODIDEMO environment  and implement following query.

select
sales.CUST_ID,CUST.CUST_NAME,
sales.PRODUCT,
sales.TOTAL_NUM_OF_ORDERS,
'%'||ROUND(   (SUM_QUANTITY*100)/SUM_QUANTITY_ALL  ) PER_QUANTITY
from
(
  select
  trg_customer.CUST_ID ,trg_customer.CUST_NAME
  from
  trg_customer where age>= 30  and  age<=50
) cust ,
(
  select
  trg_sales.CUST_ID,
  trg_product.PRODUCT ,
  SUM(trg_sales.QTY)  OVER (PARTITION BY trg_sales.CUST_ID) SUM_QUANTITY_ALL ,
  SUM(trg_sales.QTY)  OVER (PARTITION BY trg_sales.CUST_ID, trg_product.PRODUCT ) SUM_QUANTITY ,
  COUNT(trg_sales.CUST_ID)  OVER (PARTITION BY trg_sales.CUST_ID ) TOTAL_NUM_OF_ORDERS
  from
  trg_sales ,  trg_product
  where
  trg_sales.PRODUCT_ID = trg_product.PRODUCT_ID
) sales
where
sales.CUST_ID=cust.CUST_ID


At first we need prepare our datasource structure in mapping ( Sales and Customer )

Step1:
Prepare data source for sales subquery ( For this i will extract SALES and PRODUCT tables from MODEL--> SALES_ADMINISTRATION  into SALES dataset and define the Join )

Step2:
Again in Sales part  , we will gonna define our expressions
(  TOTAL_NUM_OF_ORDERS  ,SUM_QUANTITY  , SUM_QUANTITY_ALL ).
To do that , first i will drag the columns which  i need to use for calculation into expression object.
( CUST_ID , PRODUCT , QTY )

Step3:
Now i am gonna prepare CUSTOMER part of the query.For this i will extract TRG_CUSTOMER table from MODEL--> SALES_ADMINISTRATION into mapping and using filter object
( where age>= 30  and  age<=50  )

Step4:
Okay , Currently we have two different datasource  ( SALES , CUSTOMER) now according to query i will gonna merge these two set into one via join object.

Step5
As a final step , i will gonna map dataset into a target table.

Physical diagram as follows:

Note: For this example Source and Target tables are in same Oracle DB , so Default Oracle Knowledge Modules are used.

For Extraction: XKM Oracle Extract GLOBAL
For Integration: IKM  Oracle Insert  GLOBAL