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!

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

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!!

I'm not very familiar with Informix query syntax, but I would think that it should be equivalent to something like