Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Question Unanswered: ORA-01799 Outer Joining a column to a subquery

    I bet this one will stump the GURUs. Good luck with this puzzle.

    I need to do an outer join using a column where if the key is found, I need to select the max of that column. Oracle won't let me do an outer join to a subquery. Does anyone have an ideas, just using straight SQL? I can't use stored procedures or Code.

    Table1
    ID_Number, Name

    Table2
    ID_Number, Date_From, Date_To, Address

    This is the Query I need (But it's giving me a ORA-01799):

    Select Table1.ID_Number, Table1.Name, Table2.Address
    From Table1, Table2
    Where Table1.ID_Number = Table2.ID_Number(+)
    Table2.Date_From(+) = (Select max(b.Date_From) From Table2 b where Table2.ID_Number = b.ID_Number and b.Date_From <= SYSDATE and b.Date_to >= SYSDATE)

    How do I return the Table1 rows even if there are no rows in Table2?
    If I put Table2.Date_From = (Select max(b.Date_From)......... then if there are no rows in Table2 for that ID_Number, the query returns nothing, but I need it to return something.

    One more caviat, I can't do the select(max) query in the "FROM" clause. I tried it and the query would take hours to run with the millions of rows in my table.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    Select Table1.ID_Number
         , Table1.Name
         , ZZ.Address
      From Table1
    left outer
      join Table2 ZZ
        on Table1.ID_Number = ZZ.ID_Number
     where ZZ.Date_From = 
           ( Select max(Date_From) 
               From Table2 
              where Table2.ID_Number = ZZ.ID_Number 
                and Date_From <= SYSDATE 
                and Date_to >= SYSDATE
           )
    rudy
    http://r937.com/
    Last edited by r937; 08-25-03 at 22:29.

  3. #3
    Join Date
    Jun 2006
    Posts
    1
    when you put a condition in where, it results like it would be ordinary join, not left outer. so, problem with left outer and subquery inside it, still exists.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wow, michal, nice catch -- you managed to find the only post in the last three years where i made an error!!!

    change WHERE to AND and it's fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If you are in the design stage, change to long to clob or blob. Long should never be used. Even Oracle says that you shouldn't be using it for new applications.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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