Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Location
    MN
    Posts
    10

    Unanswered: your opinion on this sql please

    using oracle 9.2

    I'm converting a statement using the left outer join symbol (+) in the WHERE clause, to the ANSI standard method doing the join in the FROM clause.

    I'm I doing this convertion right?
    It just seems way too convoluted.
    --------------------------------------------------
    select count(mmatter)
    from ware.matter,
    (select udvalue,udjoin from ware.udf where udtype='MT' and udfindex=6) BrokDept,
    (select udvalue,udjoin from ware.udf where udtype='MT' and udfindex=13) ProdCode,
    (select uddate,udjoin from ware.udf where udtype='MT' and udfindex=11) ProjEndDt,
    (select udvalue,udjoin from ware.udf where udtype='MT' and udfindex=10) RenewalDt
    where matter.mmatter=BrokDept.udjoin(+) and
    matter.mmatter=ProdCode.udjoin(+) and
    matter.mmatter=ProjEndDt.udjoin(+) and
    matter.mmatter=RenewalDt.udjoin(+);


    Convert to...

    select count(ware.matter.mmatter)
    from (((ware.matter left outer join (select udvalue,udjoin from ware.udf where udtype='MT' and udfindex=6) BrokDept on matter.mmatter=BrokDept.udjoin)
    left outer join (select udvalue,udjoin from ware.udf where udtype='MT' and udfindex=13) ProdCode
    on matter.mmatter=ProdCode.udjoin)
    left outer join (select uddate,udjoin from ware.udf where udtype='MT' and udfindex=11) ProjEndDt
    on matter.mmatter=ProjEndDt.udjoin)
    left outer join (select udvalue,udjoin from ware.udf where udtype='MT' and udfindex=10) RenewalDt
    on matter.mmatter=RenewalDt.udjoin;

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    You should not need to have 4 inline views.

    Select *
    from tableA, tableB
    where tableA.id = tableB.id (+);

    is rewritten as:

    Select *
    from tableA ta
    LEFT OUTER JOIN
    tableB tb ON
    ta.id = tb.id
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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