Re: trying to convert sql left outer join to oracle

christophe CHABAS wrote:

> Try
> SAT.Survey_response_id = SR.Survey_response_id(+)
> instead of
> > SAT.Survey_response_id = (+) SR.Survey_response_id
>
> "Rich D" <rdalfonso_at_nyc.rr.com> a écrit dans le message de news:
> 196cd325.0212182258.78148c55_at_posting.google.com...
> > can anybody tell me wht this sql doesn't work in Oracle
> > I have a view in sql server that i'm converting for oracle
> >
> > SQL for SQL Server is:
> >
> > CREATE VIEW dbo.Firms_by_response
> > AS
> > SELECT dbo.Survey_Responses.Survey_response_id,
> > dbo.Survey_Answer_Texts.Survey_answer_text AS Firm,
> > dbo.Survey_Responses.Survey_instance_id
> > FROM dbo.Survey_Questions LEFT OUTER JOIN
> > dbo.Survey_Answer_Texts ON
> > dbo.Survey_Questions.Survey_questions_id =
> > dbo.Survey_Answer_Texts.Survey_questions_id RIGHT OUTER JOIN
> > dbo.Survey_Responses ON
> > dbo.Survey_Answer_Texts.Survey_response_id =
> > dbo.Survey_Responses.Survey_response_id
> > WHERE (dbo.Survey_Questions.Survey_questions_shortform = 'Firm')
> >
> >
> > I'm trying to run this for ORacle:
> >
> >
> > CREATE VIEW Firms_by_response
> > AS
> > SELECT
> > SR.Survey_response_id,
> > SR.Survey_instance_id,
> > SAT.Survey_answer_text AS Firm,
> > SAT.Survey_question_id,
> > SQ.Survey_questions_id,
> > SQ.Survey_questions_shortform
> > FROM Survey_Questions SQ, Survey_Responses SR, Survey_Answer_Texts
> > SAT
> > where
> > SQ.Survey_questions_id (+) = SAT.Survey_question_id
> > and
> > SAT.Survey_response_id = (+) SR.Survey_response_id
> > and
> > SQ.Survey_questions_shortform = 'Firm'
> >
> > I keep getting a "ORA-00936: missing expression" error
> >
> > anybody can help, thanks
> >
> > rich
> > rdalfonso_at_nyc.rr.com

And in the future be sure to indicate version and edition. Oracle 9i supports the same ANSI syntax that will be more familiar to you.

Daniel Morgan Received on Thu Dec 19 2002 - 11:27:55 CST