Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2008
    Posts
    4

    Unanswered: Translate SQL-Statment from Oracle to DB2

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2008
    Posts
    4

    Thanks a lot

    Hi r937,

    thank you very much for your translation! The sql statement works exactly like the statement for the oracle db.

    Best Regards,
    kafftarbaz

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I believe that DB2 V9.5 also supports this crappy outer-join syntax now.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •