Skip navigation
Contact Us
951 Views 2 Replies Latest reply: Nov 6, 2012 2:51 AM by Tej Ernesto RSS
Tej Ernesto Newbie 4 posts since
Oct 27, 2012
Currently Being Moderated

Nov 5, 2012 6:46 AM

How to do a self join??

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

  • Ramakrishna B.V.S. Novice 55 posts since
    Jul 25, 2010
    Currently Being Moderated
    Nov 5, 2012 9:52 PM (in response to Tej Ernesto)
    How to do a self join??

    Hi Tej Ernesto,

     

    We can join the table by itself in source

    qualifier.For that u have to write SQL Override in

    source qualifier.

    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

    database.

     

    In mapping designer,drag source table EMP into

    workspace and drag the same table again into

    workspace.

    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

     

     

     

    Regards,

    B.V.S.Ramakrishna

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points