How to implement un-pivot concept in ODI 11g

SQL Scripts used in Demo:

CREATE TABLE SALES( YEAR          VARCHAR2(4) , Q1      NUMBER(10,2) , Q2     NUMBER(10,2) , Q3      NUMBER(10,2) , Q4      NUMBER(10,2) ); / CREATE TABLE SALES_PER_QUARTER( YEAR VARCHAR2(4), QUARTER VARCHAR2(2), SALES NUMBER(10,2)); / INSERT INTO SALES VALUES('2014',23345.32,32456.41,21234.21,18324.12); INSERT INTO SALES VALUES('2015',24324.12,34234.51,25345.18,17234.86); COMMIT; /

Scenario

In this scenario, we are converting rows into columns.

Implementing un-pivot component which is available in ODI 12c (which will be available from 12.1.3 version onwards) in ODI 11g

Process:

1.      Create model

2.      Create Reverse engineer above mention datastores

3.      Create new interface

4.      Drag and drop Source datastore (SALES) and Target Datastore (SALES_PER_QUARTER)

As mention below and map accordingly.

5.      Click on “highlighted” dataset symbol (in the above diagram) which will pop-up for datasets creation.

6.      Create four datasets for four quarters as below.

7.      Now interface looks as follows

8.      For Quarter2 –Quarter Column ‘Q2’ and Sales Column <Q2_Value>

Similarly we need to repeat for Quarter3 and Quarter4

9.      Make sure that Quarter mapping should be “Staging”

10.  Select appropriate knowledge as per your requirement (In this case I am selecting IKM SQL Control Append) and flow control as “false” (because I don’t have any key on target side)

11.  Execute it.

Output:

How to dump duplicate records into one table and Non-Duplicate records into one table in ODI 12c

Pre-requisites:

Scripts:

CREATE TABLE STUDENT

(SID NUMBER(2),

NAME VARCHAR2(50),

MARKS NUMBER(3));

/

INSERT INTO STUDENT VALUES(1,'Trinesh',99);

INSERT INTO STUDENT VALUES(2,'Tejesh',98);

INSERT INTO STUDENT VALUES(3,'Bapiraju',100);

INSERT INTO STUDENT VALUES(4,'Seetharamaraju',99);

INSERT INTO STUDENT VALUES(4,'Raju',100);

INSERT INTO STUDENT VALUES(5,'Dinesh',98);

INSERT INTO STUDENT VALUES(5,'Sai',100);

INSERT INTO STUDENT VALUES(6,'Asim',100);

/

COMMIT;

/

CREATE TABLE DUP_STUDENT

AS

SELECT * FROM STUDENT WHERE 1<>1;

/

CREATE TABLE NONDUP_STUDENT

AS

SELECT * FROM STUDENT WHERE 1<>1;

Process:

1.      Reverse Engineer above datastores and make sure those are available in your model

2.      Create new mapping

3.      Drag and drop STUDENT, DUP_STUDENT and NONDUP_STUDENT datastore

4.      Drag and drop  2 Aggregate component from component palette

5.      Connect output port of Source datastore (STUDENT) to input port of aggregate component. It will pop-up as follows. Leave it as it and click ok

6.      Select Aggregate Component and go to property inspector à General à In Side having clause mention as “count(*) >1” and in side Manual group by clause mention as “STUDENT.SID” and remove all attributes except SID.

As shown in below screen

7.      Drag and drop the STUDENT datastore once again and drag and drop Join component into mapping editor from component palette

8.      Join Aggregate component 1 to STUDENT Datastore as “AGGREGATE.SID = STUDENT1.SID”

9.      Connect output port of Join component to input port for Target datastore (DUP_STUDENT). It will pop-up then disable “Create attribute On source” and click ok

10.  Now connect output port of Source Datastore (STUDENT) to input port of Aggregate component 2. It will pop-up, leave it as it and click ok

11.  Remove all attributes except “SID” from distinct Component

12.  Select Aggregate Component and go to property inspector à General à In Side having clause mention as “count(*) = 1” and in side Manual group by clause mention as “STUDENT.SID” and remove all attributes except SID

13.  Drag and drop Join Component again into mapping editor.

14.  Join Aggregate component and STUDENT1 datastore as above diagram as “AGGREGATE1.SID = STUDENT1.SID

15.  Connect output port of join to the input port of target datastore (NONDUP_STUDENT) and click ok

Completed !!!!!

How to find second or nth highest salary?

Interview Question:

Query:  To Find Second Highest Salary

Using RowId:
SELECT EMPLOYEE_ID,SALARY,RN FROM
(SELECT EMPLOYEE_ID,
SALARY,
ROW_NUMBER() OVER(ORDER BY SALARY DESC) RN
FROM EMPLOYEES)
WHERE RN =2;


Using Sub-Query:

SELECT EMPLOYEE_ID , SALARY
FROM EMPLOYEES Emp1
WHERE (2-1) = (
               SELECT COUNT(DISTINCT(Emp2.SALARY))
               FROM EMPLOYEES Emp2
               WHERE Emp2.SALARY> Emp1.SALARY
               )


Query: To Find nth Highest Salary

Using RowId:
SELECT EMPLOYEE_ID,SALARY,RN FROM
(SELECT EMPLOYEE_ID,
SALARY,
ROW_NUMBER() OVER(ORDER BY SALARY DESC) RN
FROM EMPLOYEES)
WHERE RN =<n>;


Using Sub-Query:

SELECT EMPLOYEE_ID , SALARY
FROM EMPLOYEES Emp1
WHERE (<n>-1) = (
               SELECT COUNT(DISTINCT(Emp2.SALARY))
               FROM EMPLOYEES Emp2
               WHERE Emp2.SALARY> Emp1.SALARY
               )