Skip to Content
0
Former Member
Jul 28, 2008 at 11:37 AM

left outer join BO5.1universe-migrated to BOXI left outer join missing(DB2)

91 Views

Two tables are joined using left outer join in BO 5.1 universe which is using DB2 database. When we migrated this universe to BO XI R2, the same join is existing at universe level but missing at the report level .

Before migration, query from BO 5.1 report is as follows:

SELECT

NUPB.TPBMCFOS.SYSTEM,

NUDW.TDWDAGY.AGY_ACCOUNT_SOURCE,

NUPB.TPBMCFOS.ITEMCODE_ALPHA,

NUPB.TPBMCFOS.TRANSACTION_DATE,

NUPB.TPBMCFOS.ITEMCODE_NUM,

NUPB.TPBMCFOS.AGENCY_REFERENCE,

NUPB.TPBMCFOS.POLICY_NO,

NUPB.TPBMCFOS.SUSPENSE_DATE,

NUPB.TPBMCFOS.SUSPENSE_TYPE,

NUPB.TPBMCFOS.SUSPENSE_CODE,

NUPB.TPBMCFOS.GROSS_PREMIUM,

NUPB.TPBMCFOS.COMMISSION_AMOUNT,

NUPB.TPBMCFOS.NETT_PREMIUM

FROM

NUPB.TPBMCFOS LEFT OUTER JOIN NUDW.TDWDAGY ON (NUPB.TPBMCFOS.AGENCY_REFERENCE=NUDW.TDWDAGY.AGY_ACCOUNT_REF AND NUDW.TDWDAGY.AGY_CURRENT_ROW=1)

After migration, query from DeskI report is as follows:

SELECT

NUPB.TPBMCFOS.SYSTEM,

NUDW.TDWDAGY.AGY_ACCOUNT_SOURCE,

NUPB.TPBMCFOS.ITEMCODE_ALPHA,

NUPB.TPBMCFOS.TRANSACTION_DATE,

NUPB.TPBMCFOS.ITEMCODE_NUM,

NUPB.TPBMCFOS.AGENCY_REFERENCE,

NUPB.TPBMCFOS.POLICY_NO,

NUPB.TPBMCFOS.SUSPENSE_DATE,

NUPB.TPBMCFOS.SUSPENSE_TYPE,

NUPB.TPBMCFOS.SUSPENSE_CODE,

NUPB.TPBMCFOS.GROSS_PREMIUM,

NUPB.TPBMCFOS.COMMISSION_AMOUNT,

NUPB.TPBMCFOS.NETT_PREMIUM

FROM

NUPB.TPBMCFOS,

NUDW.TDWDAGY

WHERE

( (NUPB.TPBMCFOS.AGENCY_REFERENCE=NUDW.TDWDAGY.AGY_ACCOUNT_REF AND NUDW.TDWDAGY.AGY_CURRENT_ROW=1) )