Source Qualifier Transformation

Exercise 2:Source Qualifier Transformation

Step1: Import source tables

1. Import EMP,DEPT and SALGRADE  tables from SCOTT schema to informatica source Analyzer

Step 2 : Create a target Table and import as a target

1. Open SQL Developer and type below sql query in Target schema
2. CREATE TABLE  EMP_DEPT_SAL_GRADE   (DNAME  VARCHAR2(20),DEPT_WISE_SALARY NUMBER(10,2),GRADE NUMBER);

Step3: Create mapping with the name of m_SQ1

1. 

   Go to mappings menu bar → click on create → Click on OK

2. Drag and Drop sources EMP,DEPT,SALGRADE  and target EMP_DEPT_SAL_GRADE  tables to mapping workplace
3. Delete the above SQ_DEPT,SQ_SALGRADE source qualifiers tables

4. Drag and drop DEPTNO,DNAME,LOC  from DEPT table to SQ_EMP table
5. Drag and drop GRADE,HISAL ,LOSAL from SALGRADE table to SQ_EMP table.
6. Connect DNAME ,SAL and GRADE   from source qualifier to Target table

7. Double click on Source Qualifier SQ_EMP → Click on Properties Tab
8. Click on SQL Query down arrow mark
9. In SQL Editor window provide below information
1. ODBC data source as “RRITEC_SOURCE”
2. USER NAME as: SOURCE_USER
3. Password as: SOURCE_USER

   

10. Click on Generate SQL → then automatically Query will be generated

SELECT DEPT.DNAME, EMP.SAL, SALGRADE.GRADE

FROM

EMP, DEPT, SALGRADE

WHERE

DEPT.DEPTNO=EMP.DEPTNO

11. Modify Query as shown below

SELECT DEPT.DNAME, SUM(EMP.SAL)SAL,SALGRADE.GRADE                                                                   FROM                                                               

DEPT,EMP,SALGRADE                                                               

WHERE                                                             

DEPT.DEPTNO=EMP.DEPTNO                                                

AND SAL>=LOSAL AND SAL<=HISAL                                                                     

GROUP BY                                                                       

DNAME,GRADE                                                                            

12. Click on OK → Click on OK → Save Mapping
13. Create a session and workflow
14. Run the workflow