Experts in View
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Experts in View

  1. #1
    Join Date
    Jun 2005
    Posts
    1

    Question Experts in View

    I am trying to create a View with 2 oracle table.

    Employee
    =======
    EmpID
    Empname

    EmployeeDetails
    =============
    EmpDetailID
    EmpID
    DeptID
    FromDate
    ToDate

    I have to show on screen, like below :-

    EmpID EmpName Dept1(From - To) Dept2(From - To) Dept3(From - To)
    ===================================================================

    In "EmployeeDetails" table, empid can be multiple with unique deptid .
    say,

    empid =1
    deptid =1
    fromdate=1/1/2005
    todate=2/1/2005

    empid =1
    deptid =2
    fromdate=3/23/2005
    todate=3/29/2005

    empid =1
    deptid =4
    fromdate=1/2/2005
    todate=3/18/2005

    empid =8
    deptid =2
    fromdate=1/5/2005
    todate=5/11/2005


    So, how can we create view , so that shows the output I shown above ?

    Thanks !

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    what have you come up with so far when trying it for yourself?

  3. #3
    Join Date
    Feb 2005
    Posts
    158
    CREATE TABLE a
    (EmpID NUMBER,
    DeptID NUMBER,
    FromDate DATE,
    ToDate DATE)

    BEGIN
    INSERT INTO a VALUES (1,1,'01/jan/1980','30/jan/1980');
    INSERT INTO a VALUES (1,5,'01/jan/1981','30/jan/1981');
    INSERT INTO a VALUES (1,3,'01/jan/1982','30/jan/1982');
    INSERT INTO a VALUES (2,3,'01/jan/1990','30/jan/1990');
    INSERT INTO a VALUES (2,1,'01/jan/1991','30/jan/1991');
    INSERT INTO a VALUES (2,5,'01/jan/1992','30/jan/1992');
    INSERT INTO a VALUES (3,8,'01/jan/1985','30/jan/1985');
    INSERT INTO a VALUES (3,9,'01/jan/1986','30/jan/1986');
    END;

    SELECT empid,
    MAX(deptid1), MAX(fromdate1), MAX(todate1),
    MAX(deptid2), MAX(fromdate2), MAX(todate2),
    MAX(deptid3), MAX(fromdate3), MAX(todate3)
    FROM(
    SELECT empid,
    CASE WHEN rnk = 1 THEN deptid END deptid1,
    CASE WHEN rnk = 1 THEN fromdate END fromdate1,
    CASE WHEN rnk = 1 THEN todate END todate1,
    CASE WHEN rnk = 2 THEN deptid END deptid2,
    CASE WHEN rnk = 2 THEN fromdate END fromdate2,
    CASE WHEN rnk = 2 THEN todate END todate2,
    CASE WHEN rnk = 3 THEN deptid END deptid3,
    CASE WHEN rnk = 3 THEN fromdate END fromdate3,
    CASE WHEN rnk = 3 THEN todate END todate3
    FROM
    (SELECT rank() over (PARTITION BY empid ORDER BY fromdate) rnk, a.* FROM a))
    GROUP BY empid
    Last edited by gamyers; 06-07-2005 at 07:36 PM.

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