ORACLE NOT EQUAL ( != ) CASE

Hi

In this entry i will explain the effect of null records in not equal statements.Basically null records omitted by not equal statement and Oracle shows us missing resultset.

Environment: Oracle11g


Step1:  Here below our department table which contains department_names and some rows have null department names.

Step2: Here following resultset shows us only 24 records which it should be 26
( null records are filtered by the statement  plus with  the row which department name 'Marketing')

Step3: To fix this sql statement can be typed as below. ( which we get 26 records  )

ORACLE - TIMESTAMP FORMAT

Hi

Assume that we have following data which comes from a text file.

2013-02-17-15.26.18.014055

To load this data into Oracle DB we need specific timestamp  format. ( via SQLLOADER)


Which is

TIMESTAMP "YYYY-MM-DD-HH24.MI.SS.FF"


Don try to give exact  microsecond digits  such  as

TIMESTAMP "YYYY-MM-DD-HH24.MI.SS.FFFFFF" 

That causes following error :

• ORA-01810: format code appears twice

ODI 12c Knowledge Module for Hierarchical Queries

Hi

In this entry , I will explain how to implement hierarchical queries over ODI via Knowledge Module.

Case Study:
What we  have  a table which contains employee information and for each employee there is a manager defined.Basic Rule  a manager can be an employee  which  means  while A is the manager of B , C is also can be the manager of  A  etc. Basically in oracle we can use CONNECT BY to show information about hierarchical structures and in this example we will create a KM that can implement a standart CONNECT BY query.

Query as follows:

Our Source table: ( EMPLOYEE)

Target  Table: (EMPLOYEE_HR)

Step1:
First of all we gonna create standart mapping as follows:

Filter part contains following sql:
level <= 3 AND department_id = 80

Step2:
For Execution we choose our new KM called  IKM_SQL_Control_Hierarchical

Everthing is almost as same as standart IKM SQL Control Append.We just add specific options for CONNECT BY and START WITH scripts.

KM Options as follows:

OPTIONS:
IS_NOCYCLE:  IF NULL KM use 'CONNECT BY' IF 'YES' KM USE 'CONNECT BY NOCYCLE '

START_WITH_OPTION:  IF NULL KM does not use 'START WITH' script Otherwise KM use
the value which is typed into the textbox.

CONNECT_OPTION:
KM use the value which is typed into the textbox.

When Execution completed output look like this:

For more detail you can download the Knowledge Module link below:

http://sharesend.com/ynq43t7q

ODI 12c Delete / Insert IKM Knowledge Module

Hi

In this post , i will share a IKM Knowledge Module that can make Delete/Insert operation which sometimes a better alternative for Incremental Update.

Case Study:
What i want to do is Inserting resultset of the following query into my target table FINAL_SET2 and while doing that i want to  delete the existing rows.

Sample Query:
select     CUST_ID,
CUST_NAME,
PRODUCT,
TOTAL_NUM_OF_ORDERS,
PER_QUANTITY  
 
FROM (  
select FINAL_SET.CUST_ID  CUST_ID,
FINAL_SET.CUST_NAME  CUST_NAME,
FINAL_SET.PRODUCT  PRODUCT,
FINAL_SET.TOTAL_NUM_OF_ORDERS  TOTAL_NUM_OF_ORDERS,
FINAL_SET.PER_QUANTITY  PER_QUANTITY
from ODI_DEMO.FINAL_SET FINAL_SET ,  ODI_DEMO.FINAL_SET2 TRG
where (1=1)   and (FINAL_SET.CUST_ID = TRG.CUST_ID)
    )


Step1:
At  First i am changing my target tables alias name to  TRG ( need for Knowledge Module )

My Source table :

My Target Table:  (FINAL_SET2)

What i expect is executing my mapping with my new IKM and replace the three rows showed above with the ones  in the source table.



Step2:
Preparing mapping object  according to our query.

Step3:
Setting my Knowledge Module

Step4:
The KM basically has  3 steps. First i am creating a temp table which is as same as my target table.By this way before deleting the rows in my target  , i can save the resulset into another table for upcoming insert operation.

Step5:
We just inserted the resulset into temp table and now we can delete the existing records.

Step6:
And for final step, New resultset data which is inside of our temp table will be inserted into  our target table.

Final view of our target table:

Note:
You can download the KM file below:
http://speedy.sh/dwcx4/KM-IKM-SQL-Control-DeleteInsert-2.xml

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

ODI 12c Subquery Filter

Hi

In this entry i am gonna show how to implement an oracle pl/sql statement with subquery condition in ODI 12c.


Environment : ODI 12c  & Oracle 11g

Sample Query which i will implement:

select  custid 

from src_customer

where

custid   in

(

select 

CUST_ID

from src_orders

group by CUST_ID

having count(order_id) >3

)

Tables that we are using for this implementation:

 Source table 1:

 Source table 2:

Target Table:

Mapping:

In above you can see the mapping we suppose use for this operation.Trick is hidden inside of the subquery filter object.

ODI  already automatically set SRC_ORDERS table as a subquery table.Now only thing we have to do is to set Subquery filter filter properly .( Check the image above).For KMs standart knowledge modules works fine , you dont have to do any change.( For this example LKM Exract and IKM Oracle Global Knowledge modules were used. )

ORACLE - Add List partition with for loop

Following script let you create list partition  between dates you specify.
Dates  , tablename and tablespace are all depend on variables that you can change manually.

DECLARE

p_BAS_TAR  DATE  ;

p_BIT_TAR   DATE;

pTABLE_NAME VARCHAR2( 500 CHAR);

pTABLESPACE_NAME  VARCHAR2( 500 CHAR);

DIFF NUMBER ;

INC_VAL  NUMBER :=0;

V_DYNTASK LONG;

BEGIN

DIFF :=0;

p_BAS_TAR:= TO_DATE('20140316','YYYYMMDD');  -- INCULING THIS DAY

p_BIT_TAR:= TO_DATE('20200101','YYYYMMDD');  -- TILL THIS DAY NOT INCLUDED

pTABLE_NAME := 'TABLE_NAME;  --   TABLE_NAME

pTABLESPACE_NAME:='TABLESPACE_NAME';  -- TABLESPACE_NAME

SELECT trunc(TO_DATE(''||p_BIT_TAR||'') )  -  TO_date(TO_DATE(''||p_BAS_TAR||'', 'dd/mm/yyyy') )     INTO  DIFF FROM DUAL;

DBMS_OUTPUT.PUT_LINE(DIFF);

FOR Lcntr IN 1..DIFF

LOOP

  

V_DYNTASK:='ALTER TABLE '||pTABLE_NAME||' ADD PARTITION P_'||to_char(p_BAS_TAR, 'YYYYMMDD')||' VALUES   (TO_DATE('''||p_BAS_TAR||''', ''dd.mm.yyyy'')) '||(case when pTABLESPACE_NAME is null then null else 'TABLESPACE '||pTABLESPACE_NAME end);

DBMS_OUTPUT.PUT_LINE(V_DYNTASK);

EXECUTE IMMEDIATE V_DYNTASK;

COMMIT;

p_BAS_TAR :=  p_BAS_TAR  +  1;

END LOOP;

END;