Re: SQL Server to ORACLE syntax

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 16 Feb 2003 10:25:19 -0800
Message-ID: <b2ol2f01ekl@drn.newsguy.com>

In article <196cd325.0302160019.7b7fba7f_at_posting.google.com>, rdalfonso_at_nyc.rr.com says...
>
>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

Syntax is fine cept for the extra comma you have after firms_by_response.firm:

ops$tkyte_at_ORA920> CREATE or replace VIEW Survey_opt_ans_by_instance   2 AS
  3 SELECT

  4  Survey_Responses.Survey_response_id,
  5  Survey_Questions.Survey_questions_text_label,
  6  Survey_Questions.Survey_questions_sequence_id,
  7  Survey_Question_Options.Survey_ques_opts_seq_id,
  8  Survey_Responses.Survey_instance_id,

  9 QuestionTypeForQuestionId(Survey_Questions.Survey_questions_id) AS  10 QuestionType,
 11 RTRIM(Survey_Questions.Survey_questions_name) + ':' +  12 RTRIM(Survey_Question_Options.Survey_question_options_text) AS  13 Question,
 14 CASE WHEN Survey_Answers.Survey_answer_id IS NULL THEN 'False' ELSE  15 'True' END AS Answer,
 16 Firms_by_response.Firm
 17 FROM
 18 Survey_Answers RIGHT OUTER JOIN

 19          Survey_Responses INNER JOIN
 20                        Survey_Questions ON

 21 Survey_Responses.Survey_instance_id =  22 Survey_Questions.Survey_instance_id INNER JOIN

 23                        Firms_by_response ON

 24 Survey_Responses.Survey_response_id =  25 Firms_by_response.Survey_response_id INNER JOIN

 26                        Survey_Question_Options ON
 27  Survey_Questions.Survey_questions_id =
 28  Survey_Question_Options.Survey_questions_id ON

 29 Survey_Answers.Survey_question_option_id =  30 Survey_Question_Options.Survey_question_opts_id  31 AND Survey_Answers.Survey_response_id =  32 Survey_Responses.Survey_response_id  33 AND Survey_Answers.Survey_question_id =  34 Survey_Questions.Survey_questions_id  35 WHERE (Survey_Questions.Survey_ques_ff_txt_flag = 0)  36 ORDER BY Survey_Responses.Survey_response_id,  37 Survey_Questions.Survey_questions_sequence_id,  38 Survey_Question_Options.Survey_ques_opts_seq_id  39 /

View created.

Note you will have to reduce the size of some identifiers to 30 characters (you can start by losing the "application name as part of the column name". I reduced them to compile the view but it is otherwise unchanged.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sun Feb 16 2003 - 12:25:19 CST