Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: Derived Tables in Oracle?

    Aloha

    Hey,

    This Works

    Code:
           SELECT 'INTERVIEW' AS TABLE_NAME, STATUSDATE, STATUS, '' AS STATUSREASON, CANDIDATEID
             FROM INTERVIEW
        UNION ALL
           SELECT 'OFFER' AS TABLE_NAME, STATUSDATE, OFFERSTATUS, STATUSREASON, CANDIDATEID
             FROM OFFER
        UNION ALL   
          SELECT 'SCREEN' AS TABLE_NAME, STATUSDATE, STATUS, '' AS STATUSREASON, CANDIDATEID
             FROM INTERVIEW
    But this does not

    Code:
       SELECT TABLE_NAME, STATUSDATE, STATUS, STATUSREASON, CANDIDATEID
         FROM (
           SELECT 'INTERVIEW' AS TABLE_NAME, STATUSDATE, STATUS, '' AS STATUSREASON, CANDIDATEID
             FROM INTERVIEW
        UNION ALL
           SELECT 'OFFER' AS TABLE_NAME, STATUSDATE, OFFERSTATUS, STATUSREASON, CANDIDATEID
             FROM OFFER
        UNION ALL   
          SELECT 'SCREEN' AS TABLE_NAME, STATUSDATE, STATUS, '' AS STATUSREASON, CANDIDATEID
             FROM INTERVIEW
    ) AS XXX;
    I'm trying to make that a derived table that I then can join the results to

    Any ideas?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Leave out the "AS" on all the statements and it should work fine.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Great, thanks, worked like a champ...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Which became....

    Code:
       SELECT xxx.TABLE_NAME, xxx.STATUSDATE, xxx.STATUS, xxx.STATUSREASON
            , xxx.CANDIDATEID, c.PERSONID, p.LASTNAME, p.FIRSTNAME
         FROM (
           SELECT 'INTERVIEW' AS TABLE_NAME, STATUSDATE, STATUS, '' AS STATUSREASON, CANDIDATEID
             FROM DEPLOY.INTERVIEW
        UNION ALL
           SELECT 'OFFER' AS TABLE_NAME, STATUSDATE, OFFERSTATUS, STATUSREASON, CANDIDATEID
             FROM DEPLOY.OFFER
        UNION ALL   
          SELECT 'SCREEN' AS TABLE_NAME, STATUSDATE, STATUS, '' AS STATUSREASON, CANDIDATEID
             FROM DEPLOY.INTERVIEW
    ) XXX
     LEFT JOIN DEPLOY.CANDIDATE c
            ON  xxx.CANDIDATEID = c.CANDIDATEID
     LEFT JOIN DEPLOY.PERSON p
            ON p.PERSONID = c.PERSONID
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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