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