Hello,

i am new here and i am sorry, that my english is not so good. I have problems to translate the following sql-statement from oracle to IBM DB2. If you can help me, i will by very happy....

Thanks a lot in advance!


That 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
;

1 thing I see that's "oracle only" are the (+) symbols, which indicate an outer join.
Convert them to proper SQL left or right outer joins.

another thing are the

=:questStaticId

and

= :questionnaireId

which indicate that you are feeding a variable to your query (or cursor, probably...).
But for that, I don't know how to translate in DB2...

Just for the record, I never worked with DB2, but I know that the + is Oracle only.

Hello tripy,

thanks for your tipps, now I have a solution:

the following changes do it:

======
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 = 1
where
chap.q_questionnaire_id = 1
======


best regards,
kafftarbaz