Re: Oracle outer join syntax (+) not working???

chilecayenne_at_yahoo.com wrote:
> Again...I'm rusty, and have no documentation around...probably a stupid
> question.
>
> I'm trying to join on a field that has some null values using (+), but,
> oracle doesn't seem to recognize this anymore. I'm on Oracle 9.2.0.6.
>
> I've been looking up, an seeing that there is a new way to do an outter
> join, but, most examples I see only are using 2 tables and a couple of
> fields, I can't figure how to use this on a large multi-table join
> where lots of fields are being selected. Can someone help me with an
> example of point to a good link somewhere?
>
> Here's what I'm trying to do:
>
> select a.name, a.projectid, b.title another_title,
> c.trainingrequirement, d.title need_title, d.description
> some_need_description, NVL(f.catalognumber,'NULL Catalog Number')
> catalognumber, f.courseid, f.title course_title, NVL
> (h.proficiencylevel,'NULL Proficiency Level') proficiency,
> h.proficiencylevelid, e.candidatecourseid, e.notes team_lead_notes
> from table1 a, table2 b, table3 c, table4 d, table5 e, table6 f,
> table7 h
> where a.categorytemplateid = b.categorytemplateid
> and a.projectid = c.projectid
> and c.trainingneedid = d.trainingneedid
> and c.projecttrainingneedid = e.projecttrainingneedid
> and e.courseid = f.courseid
> and (+) e.proficiencylevelid = h.proficiencylevelid
> and upper(a.projectname) like '%SOMESTUDY%'
> and f.courseid in (6740,2035)
> order by a.projectname, c.trainingrequirement, f.title
>
> This keeps freaking out on me when it hits the (+) sign...did they
> throw out this functionality? I can't figure how to do the new one from
> what I've found so far on the web...and I've got no documentation handy
> due to losses from Katrina.
>
> Thanks in advance,
>
> chilecayenne

No change has been made to Oracle with respect to the ISO syntax. The syntax error is yours.

e.proficiencylevelid(+) = h.proficiencylevelid

would be correct. The way you did it has never worked TTBOMK.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org