Translate SQL-Statment from Oracle to DB2

kafftarbaz · #1 (**permalink**) 01-28-08, 08:21

Hello,

I am new here and I have a problem by translate sql-statement from oracle database to IBM DB2. I f you can help me, I will be very happy, thanks in advance.

Here is the SQL-Statement:

select
chap.q_chapter_id as CHAP_Q_CHAPTER_ID,
chap.numeration as CHAP_NUMERATION,
chap.description as CHAP_DESCRIPTION,
chap.name as CHAP_NAME,
chap.weight as CHAP_WEIGHT,
chap.identifier as CHAP_IDENTIFIER,
chap.threshold as CHAP_THRESHOLD,
sect.q_section_id as SECT_Q_SECTION_ID,
sect.description as SECT_DESCRIPTION,
sect.numeration as SECT_NUMERATION,
sect.name as SECT_NAME,
sect.weight as SECT_WEIGHT,
sect.threshold as SECT_THRESHOLD,
sect.identifier as SECT_IDENTIFIER,
groups.q_question_group_id as GROUPS_Q_QUESTION_GROUP_ID,
groups.description as GROUPS_DESCRIPTION,
groups.numeration as GROUPS_NUMERATION,
groups.weight as GROUPS_WEIGHT,
groups.name as GROUPS_NAME,
groups.identifier as GROUPS_IDENTIFIER,
grpval.q_question_group_id as GRPVAL_Q_QUESTION_GROUP_ID,
grpval.q_questionnaire_statistics_id as GRPVAL_Q_QUESTSTAT_ID,
grpval.value as GRPVAL_VALUE,
grpval.to_be_clarified as GRPVAL_TO_BE_CLARIFIED,
grpval.os_value as GRPVAL_OS_VALUE
from
q_chapters chap, q_sections sect, q_question_groups groups, q_question_group_values grpval
where
chap.q_questionnaire_id = :questionnaireId
and sect.q_chapter_id(+) = chap.q_chapter_id
and groups.q_section_id(+) = sect.q_section_id
and grpval.q_question_group_id(+) = groups.q_question_group_id
and grpval.q_questionnaire_statistics_id(+) = :questStaticId
order by
CHAP_Q_CHAPTER_ID, SECT_Q_SECTION_ID, GROUPS_Q_QUESTION_GROUP_ID, GRPVAL_Q_QUESTION_GROUP_ID
;

This should be translated to IBM DB2 dialect.

Best Regadrs,
Kafftarbaz

r937 · #2 (**permalink**) 01-28-08, 08:31

change this --

Code:

from
q_chapters chap, q_sections sect, q_question_groups groups, q_question_group_values grpval 
where
chap.q_questionnaire_id = :questionnaireId
and sect.q_chapter_id(+) = chap.q_chapter_id 
and groups.q_section_id(+) = sect.q_section_id
and grpval.q_question_group_id(+) = groups.q_question_group_id
and grpval.q_questionnaire_statistics_id(+) = :questStaticId

to this --

Code:

  from q_chapters chap
left outer
  join q_sections sect
    on sect.q_chapter_id = chap.q_chapter_id 
left outer
  join q_question_groups groups
    on groups.q_section_id = sect.q_section_id
left outer
  join q_question_group_values grpval 
    on grpval.q_question_group_id = groups.q_question_group_id
   and grpval.q_questionnaire_statistics_id = :questStaticId
 where chap.q_questionnaire_id = :questionnaireId