Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    7

    Unanswered: Joins from 9i to 8i HELP

    I'm converting a bunch of queries from 9i to 8i, but not very familiar with the 9i join syntax. I don't understand all these parentheses!! Can someone explain the logical order, and if you're bored, convert this statement to 8i syntax for me? Here it is:

    SELECT RtuToComPath.RTU_NB, RtuToComPath.NAME, DeviceTypeLookup.DESCR AS DEVICE, ServiceTypeLookup.DESCR AS SERVICE, ConvergeCompath.NAME AS MODEL, ConvergeCompath.TYPE, RtuToComPath.STATUS, RtuToComPath.MODEL_COM_PATH_ID, C2000Rtu.TYPE AS C2000_Type, C2000Rtu.SERVICE AS C2000_Service, RtuToComPath.COM_PATH_ID, C2000RtuGr.GR_NAME, C2000RtuAcqList.LIST_NAME
    FROM ServiceTypeLookup INNER JOIN (DeviceTypeLookup INNER JOIN ((C2000RtuGr RIGHT JOIN C2000RtuGrAss ON C2000RtuGr.GR_NB = C2000RtuGrAss.GR_NB) RIGHT JOIN ((C2000RtuAcqList RIGHT JOIN C2000RtuAcqListAss ON C2000RtuAcqList.LIST_NB = C2000RtuAcqListAss.LIST_NB) RIGHT JOIN ((RtuToComPath INNER JOIN C2000Rtu ON RtuToComPath.RTU_NB = C2000Rtu.RTU_NB) LEFT JOIN ConvergeCompath ON RtuToComPath.MODEL_COM_PATH_ID = ConvergeCompath.COM_PATH_ID) ON C2000RtuAcqListAss.RTU_NB = C2000Rtu.RTU_NB) ON C2000RtuGrAss.RTU_NB = C2000Rtu.RTU_NB) ON DeviceTypeLookup.TYPE = C2000Rtu.TYPE) ON ServiceTypeLookup.TYPE = C2000Rtu.SERVICE
    WHERE (((RtuToComPath.STATUS)=2));

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Joins from 9i to 8i HELP

    That will be a tricky task I think!

    But the code looks like it was generated by a program, judging by the number of unnecessary parentheses and the lack of any readable formatting. If so, maybe that program has an option to generate 8i syntax instead of using ANSI joins?

  3. #3
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    7

    Re: Joins from 9i to 8i HELP

    You're right... This was a code from Microsoft Access. Which, as far as I know, can't create a "normal" query without JOIN syntax to be 8i compatible. So it's up to me to make it look nice for 8i. Any Access pros out there? It seems when you use "right join" in access, it's equivolent to the (+) being on the LEFT??? Which is opposite of how Oracle treats RIGHT or LEFT JOIN. With Oracle, LEFT JOIN would be the same as the (+) being on the left.

    Any thoughts??

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Joins from 9i to 8i HELP

    No, you are wrong there. In Oracle:

    SELECT ... FROM a LEFT JOIN b ON a.x = b.x;

    is equivalent to:

    SELECT ... FROM a, b
    WHERE a.x = b.x (+);

    I believe the meaning is that the table "on the left" (i.e. a) is protected from "losing" rows when joined to b. (But the terms LEFT and RIGHT are pretty dumb IMHO!)

  5. #5
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    7
    crazy.... thanks! I got my info from "OCP Introduction to Oracle9i: SQL Exam Guide" by Jason Couchman, pp 112-113:

    select e.ename, e.deptno, d.dname
    from dept d, emp e
    where d.deptno = e.deptno (+)

    same as

    select e.ename, e.deptno, d.dname
    from emp e right outer join dept d
    on d.deptno = e.deptno


    So.............. is this book wrong then??

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, because the positions of emp and dept have been switched!

    select e.ename, e.deptno, d.dname
    from emp e right outer join dept d
    on d.deptno = e.deptno

    same as

    select e.ename, e.deptno, d.dname
    from dept d left outer join emp e
    on d.deptno = e.deptno

  7. #7
    Join Date
    Jan 2004
    Posts
    492
    Am I the only one who really hates the INNER JOIN ON syntax?

    For me looking at
    WHERE EMP.DEPTNO = DEPT.DEPTNO makes a lot more sense than trying to find out which columns are inner joined.

    And the left and right joins are even worse. I hate when I look at a program in the ANSI SQL syntax because it takes me forever to figure out whats even being selected!

    Just my 2 cents.

  8. #8
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    I agree. I do not like "NANSI" joins...

    But they are very useful when you have to outer join more than 1 table together.

Posting Permissions

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