Re: SQL Server to ORACLE syntax

Rich D wrote:
>
> I need to convert the following view from SQL Server to ORACLE
> Can anybody show me the oracle equivalent?
>
> CREATE VIEW Survey_option_answers_by_instance
> AS
> SELECT
> Survey_Responses.Survey_response_id,
> Survey_Questions.Survey_questions_text_label,
> Survey_Questions.Survey_questions_sequence_id,
> Survey_Question_Options.Survey_question_options_sequence_id,
> Survey_Responses.Survey_instance_id,
> QuestionTypeForQuestionId(Survey_Questions.Survey_questions_id) AS
> QuestionType,
> RTRIM(Survey_Questions.Survey_questions_name) + ':' +
> RTRIM(Survey_Question_Options.Survey_question_options_text) AS
> Question,
> CASE WHEN Survey_Answers.Survey_answer_id IS NULL THEN 'False' ELSE
> 'True' END AS Answer, Firms_by_response.Firm,
> FROM
> Survey_Answers RIGHT OUTER JOIN
> Survey_Responses INNER JOIN
> Survey_Questions ON
> Survey_Responses.Survey_instance_id =
> Survey_Questions.Survey_instance_id INNER JOIN
> Firms_by_response ON
> Survey_Responses.Survey_response_id =
> Firms_by_response.Survey_response_id INNER JOIN
> Survey_Question_Options ON
> Survey_Questions.Survey_questions_id =
> Survey_Question_Options.Survey_questions_id ON
> Survey_Answers.Survey_question_option_id =
> Survey_Question_Options.Survey_question_options_id
> AND Survey_Answers.Survey_response_id =
> Survey_Responses.Survey_response_id
> AND Survey_Answers.Survey_question_id =
> Survey_Questions.Survey_questions_id
> WHERE (Survey_Questions.Survey_questions_freeform_text_flag = 0)
> ORDER BY Survey_Responses.Survey_response_id,
> Survey_Questions.Survey_questions_sequence_id,
> Survey_Question_Options.Survey_question_options_sequence_id

I do not see a problem with that syntax under Oracle 9.2. Assuming that the function "QuestionTypeForQuestionId" is working properly. Oracle 8.1 and earlier do not support the explicit inner and outer join syntax you are using, not do they support the case statement. I would suggest that you put the case statement in parems, though.

-- 
Andrew Allen