Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    india
    Posts
    67

    Unanswered: Convert the follwoing query to ANSI-92 standards

    Dear Gurus,

    I have difficulty in Converting Following SQL query to ANSI-92 standards, Please help me.

    SELECT <<what ever>>
    FROM tableA A, TableB B,Table C,Table D
    WHERE a.LIST_ID(+) = b.INCL_LIST_ID
    AND d.LIST_ID(+) = b.EXCL_LIST_ID
    AND c.ID = d.HIER_ID(+);

    Thanx in Advance

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    I doubt the query you posted ever worked (although it is possible to write it in old Oracle syntax).
    As you did not provide created table scripts, I created a little testcase, ran on Oracle 10.1.0.3:
    Code:
    SQL> create table t1( c1, c2, c3, c4 ) as
      2  select level, level+1, level+2, level+3
      3  from dual
      4  connect by level <= 4;
    
    Table created.
    
    SQL> select * from t1;
    
            C1         C2         C3         C4
    ---------- ---------- ---------- ----------
             1          2          3          4
             2          3          4          5
             3          4          5          6
             4          5          6          7
    
    SQL> select a.c1, b.c1, c.c1, d.c1
      2  from t1 a, t1 b, t1 c, t1 d
      3  where a.c1(+) = b.c2
      4    and d.c4(+) = b.c2
      5    and c.c3    = d.c4(+);
      and d.c4(+) = b.c2
                  *
    ERROR at line 4:
    ORA-01417: a table may be outer joined to at most one other table
    
    
    SQL>
    The error message is quite clear.

Posting Permissions

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