Right Outer join (sql92 - Oracle)

[wwworg]

Hey guys
I have a sql statement that contains an inner and outer join, but when I run that sql statement it gives me an error on "oj", saying "Non supported SQL92 token at position: 217: oj" . I was running this sql statement agaibst Oracle 8i. So I guess oracle doesnt like "oj".

This is the sql statement in SQL92

select * from {oj tableA INNER JOIN tableB ON tableA.number = tableB.number
RIGHT OUTER JOIN JOBS ON JOBS.number = tableB.number}

I have converted the above sql statement into the format that should be acceptable by oracle 8i. Please let me know if this should work or not.

select * from tableA, tableB, JOBS where tableA.number = tableB.number and JOBS.number (+) = tableB.number.

If this is not the right conversion than I will appreciate if you can supply the code for it.

Thanks

 

[SantaMufasa]

Org,

Your re-write looks syntactically fine for Oracle, provided that your column name is not really "number" (Oracle key word). The "desk-check" analysis of your SELECT means that rows will print out...

If/when there are matches between tableA's and tableB's <number> columns and they must also match Jobs's <number> column, but if there is no match in the Jobs table, the matching rows for the other two tables will still print out.

Also, as a convenience, you can use table aliases in the following manner:

select * from tableA a, tableB b, JOBS j
 where A.number = B.number
   and J.number (+) = B.number;

Let us know if you have any follow-on questions.



Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SimonSellick]

http://wwworg,

I think that your Oracle translation specifies a LEFT OUTER join, whereas your original query specified RIGHT OUTER. Do you want a tableA + tableB row irrespective of the existence of matching JOBS rows, or a JOBS row irrespective of the existence of matching tableA + tableB rows?

HTH