I am using EMP table in SCOTT schema i want employee name and their manager name...
I dragged EMP table twice and removed source qualifer for one EMP and dragged all the colums in one table to the common source qalifier...
and then i wrote join condition but its not working can someone solve my problem
Hi Tej Ernesto,
We can join the table by itself in source
qualifier.For that u have to write SQL Override in
write this query in source qualifier.
SELECT A.EMPNO,A.ENAME,A.JOB,B.ENAME MGR_NAME FROM EMP
A LEFT OUTER JOIN EMP B ON A.MGR=B.EMPNO
Here EMP is the table in SCOTT schema in ORACLE
In mapping designer,drag source table EMP into
workspace and drag the same table again into
Now u have two EMP tables with names EMP & EMP1.
delete one source qualifier and drag column ENAME from
this table to existing source qualifier.Write above
sqloverride in source qualifier and complete the
mapping with target.
u will get the following output:
EMPNO ENAME JOB MGR_NAME
7902 FORD ANALYST JONES
7788 SCOTT ANALYST JONES
7900 JAMES CLERK BLAKE
7844 TURNER SALESMAN BLAKE
7654 MARTIN SALESMAN BLAKE
7521 WARD SALESMAN BLAKE
7499 ALLEN SALESMAN BLAKE
7934 MILLER CLERK CLARK
7876 ADAMS CLERK SCOTT
7782 CLARK MANAGER KING
7698 BLAKE MANAGER KING
7566 JONES MANAGER KING
7369 SMITH CLERK FORD
7839 KING PRESIDENT
You can go through the following solution from Informatica Marketplace. You will also find over 400 mapping samples and many other useful tools on the same website.