DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: confused of solution to this query

  1. #1
    Join Date
    Aug 2007
    Posts
    18

    confused of solution to this query

    in emp table ename column stores employees information and also managers information, so i want to query in the following output

    mname empno ename

    Blake 7499 Allen
    7521 Ward
    7900 James
    7844 Turner
    7654 Martin

    Clark 7934 Miller

    Ford 7369 Smith

    Jones 7788 Scott
    7902 Ford


    so on the output should be generated
    mname is manager name
    ename is employee name

  2. #2
    Join Date
    Apr 2006
    Posts
    377
    Code:
    SQL> select 'mname is ' || case level when 1 then 'I''m the BOSS !!'
      2                        else connect_by_root ename end || chr(10) ||
      3         'ename is ' || ename || chr(10) "Employees and Managers"
      4  from scott.emp
      5  where level = 2
      6  or mgr is null
      7  connect by prior empno = mgr;
    
    Employees and Managers
    ---------------------------------------------
    mname is SCOTT
    ename is ADAMS
    
    mname is FORD
    ename is SMITH
    
    mname is JONES
    ename is SCOTT
    
    mname is JONES
    ename is FORD
    
    mname is CLARK
    ename is MILLER
    
    mname is BLAKE
    ename is ALLEN
    
    mname is BLAKE
    ename is WARD
    
    mname is BLAKE
    ename is JAMES
    
    mname is BLAKE
    ename is TURNER
    
    mname is BLAKE
    ename is MARTIN
    
    mname is I'm the BOSS !!
    ename is KING
    
    mname is KING
    ename is JONES
    
    mname is KING
    ename is CLARK
    
    mname is KING
    ename is BLAKE
    
    
    14 rows selected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width