String concat is || not + in Oracle.
Jim
--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Andrew Allen" <ajallen_at_mailhost.det.ameritech.net> wrote in message
news:3E4F9A11.FF7C2CAF_at_mailhost.det.ameritech.net...
> 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
Received on Sun Feb 16 2003 - 10:09:48 CST
