Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2008
    Posts
    2

    Unanswered: Convert Informix OUTER to DB2 syntax

    Hi All,
    I am converting some sql code from informix to DB2. I am stuck at one of them which is using two outer statements. Here is the informix sql:

    ----------
    SELECT DISTINCT db_pmt_detail.n_bpmt_dtl_oid,
    policy_bill_group.n_pol, policy_bill_group.n_pol_mod, bill_account.n_acct, db_pmt_detail.c_bpmt_typ, bill_trn_typ.t_btrn_desc, sum(db_pmt_itm.a_dbpi)*-1, db_pmt_itm.d_bpmi_run, cash_pmt_detail.n_cpd_doc

    FROM db_pmt_itm, db_pmt_detail, bill_installment,

    outer policy_bill_group,
    outer bill_trn_typ,

    bill_account,
    cash_pmt_detail, cash_pmt_entity

    WHERE bill_account.n_acct_oid = 1234567 and bill_account.n_acct_oid = bill_installment.n_acct_oidand db_pmt_itm.n_inst_oid = bill_installment.n_inst_oidand db_pmt_itm.n_bpmt_dtl_oid = db_pmt_detail.n_bpmt_dtl_oidand bill_installment.n_pbgrp_oid = policy_bill_group.n_bgrp_oid and db_pmt_detail.c_bpmt_typ = bill_trn_typ.c_btrn_typand cash_pmt_detail.n_cpd_oid = cash_pmt_entity.n_cpd_oid and db_pmt_detail.n_cpe_oid = cash_pmt_entity.n_cpe_oid GROUP BY db_pmt_detail.n_bpmt_dtl_oid, policy_bill_group.n_pol, policy_bill_group.n_pol_mod, bill_account.n_acct, db_pmt_detail.c_bpmt_typ, bill_trn_typ.t_btrn_desc, db_pmt_itm.d_bpmi_run, n_cpd_doc
    ORDER BY db_pmt_itm.d_bpmi_run, policy_bill_group.n_pol desc
    ----------

    I figured out that you need to use LAST table from FROM clause to join the table in outer join, so I modified this sql with this one:


    SELECT DISTINCT db_pmt_detail.n_bpmt_dtl_oid, policy_bill_group.n_pol, policy_bill_group.n_pol_mod,
    bill_account.n_acct, db_pmt_detail.c_bpmt_typ, bill_trn_typ.t_btrn_desc,
    sum(db_pmt_itm.a_dbpi)*-1, db_pmt_itm.d_bpmi_run, cash_pmt_detail.n_cpd_doc

    FROM DB2I.db_pmt_itm db_pmt_itm, DB2I.db_pmt_detail db_pmt_detail,

    OUTER DB2I.bill_trn_typ bill_trn_typ,

    DB2I.bill_account bill_account,

    DB2I.cash_pmt_detail cash_pmt_detail, DB2I.cash_pmt_entity cash_pmt_entity,

    DB2I.bill_installment bill_installment

    LEFT OUTER JOIN DB2I.policy_bill_group policy_bill_group ON bill_installment.n_pbgrp_oid = policy_bill_group.n_bgrp_oid

    WHERE bill_account.n_acct_oid = 1234567 and bill_account.n_acct_oid = bill_installment.n_acct_oid

    and db_pmt_itm.n_inst_oid = bill_installment.n_inst_oid and db_pmt_itm.n_bpmt_dtl_oid = db_pmt_detail.n_bpmt_dtl_oid

    --and bill_installment.n_pbgrp_oid = policy_bill_group.n_bgrp_oid

    and db_pmt_detail.c_bpmt_typ = bill_trn_typ.c_btrn_typ

    and cash_pmt_detail.n_cpd_oid = cash_pmt_entity.n_cpd_oid and db_pmt_detail.n_cpe_oid = cash_pmt_entity.n_cpe_oid

    GROUP BY db_pmt_detail.n_bpmt_dtl_oid, policy_bill_group.n_pol, policy_bill_group.n_pol_mod, bill_account.n_acct,

    db_pmt_detail.c_bpmt_typ, bill_trn_typ.t_btrn_desc, db_pmt_itm.d_bpmi_run, n_cpd_doc

    ORDER BY db_pmt_itm.d_bpmi_run, policy_bill_group.n_pol desc
    ---------------------------------------

    This took care of one outer join. How do I take care of the other outer statement?

    THANKS!

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The outer join does not have to be LAST. It has to follow the table it is being outer joined to. You can try the below, it should be pretty close or at least give you a template to follow.

    SELECT DISTINCT db_pmt_detail.n_bpmt_dtl_oid
    , policy_bill_group.n_pol
    , policy_bill_group.n_pol_mod
    , bill_account.n_acct
    , db_pmt_detail.c_bpmt_typ
    , bill_trn_typ.t_btrn_desc
    , sum(db_pmt_itm.a_dbpi)*-1
    , db_pmt_itm.d_bpmi_run
    , cash_pmt_detail.n_cpd_doc
    FROM bill_account
    inner join bill_installment,
    on bill_account.n_acct_oid = bill_installment.n_acct_oid

    left outer join policy_bill_group
    on bill_installment.n_pbgrp_oid = policy_bill_group.n_bgrp_oid

    inner join db_pmt_itm
    on db_pmt_itm.n_inst_oid = bill_installment.n_inst_oid

    inner join db_pmt_detail
    on db_pmt_itm.n_bpmt_dtl_oid = db_pmt_detail.n_bpmt_dtl_oid

    left outer join bill_trn_typ
    on db_pmt_detail.c_bpmt_typ = bill_trn_typ.c_btrn_typ

    inner join cash_pmt_entity
    on db_pmt_detail.n_cpe_oid = cash_pmt_entity.n_cpe_oid

    inner join cash_pmt_detail
    on cash_pmt_detail.n_cpd_oid = cash_pmt_entity.n_cpd_oid

    WHERE bill_account.n_acct_oid = 1234567
    GROUP BY db_pmt_detail.n_bpmt_dtl_oid
    , policy_bill_group.n_pol
    , policy_bill_group.n_pol_mod
    , bill_account.n_acct
    , db_pmt_detail.c_bpmt_typ
    , bill_trn_typ.t_btrn_desc
    , db_pmt_itm.d_bpmi_run
    , n_cpd_doc
    ORDER BY db_pmt_itm.d_bpmi_run
    , policy_bill_group.n_pol desc

  3. #3
    Join Date
    Jul 2008
    Posts
    2

    DB2 Outer joins

    Thank you so much for the explanation. It makes perfect sense now.
    Just one more question, in an informix query I have something like this:
    Select a, b, c
    from table1, table 2
    outer (table3, table 4)
    where .....

    If I modify this query for DB2 like this, would it produce similar results?

    Select a, b, c
    from table 1
    inner join table 2 on table1.x = table2.x
    left outer join table 3 on table 1.x = table3.x
    left outer join table4 on table1.x = table4.x

    Thanks!!

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm not very familiar with Informix query syntax, but I would think that it should be equivalent to something like
    Code:
    Select a, b, c
    from table 1
    inner join table 2 on table1.x = table2.x
    left outer join (table3 inner join table4 on  table3.x = table4.y)
    on table1.x = table4.x
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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