Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

We recently migrated to 8.2.3 from 7.7.6 and we are having d...


Brad Gronli

Recommended Posts

We recently migrated to 8.2.3 from 7.7.6 and we are having difficulties with a LEFT OUTER JOIN. This is not including those records from the left which do not match on the right. Since it should include those from the left and those that match, I am at a loss as to how to correct this. Ive looked at the SQL trace and to me it does not look like it would produce the report correctly. I would expect it have a LEFT OUTER JOIN within it, since it is specified in the JOIN edit window.

Detail:

(FOC2689) AGGREGATION DONE

SELECT

T1.L3_CD,

T1.L4_CD,

T1.L5_CD,

T1.CAT_EXT,

T1.OBJ,

T1.OCA,

T1.CAT,

SUM(T1.ALMT_YTD),

SUM(T1.EXP_MTD),

SUM(T1.EXP_YTD),

SUM(T1.ENC_YTD),

SUM(T1.ALMT_BAL)

FROM

IWHSV.ALMTBAL T1,

IWHSV.ZOBJECT T2

WHERE

(T2.OBJ = T1.OBJ) AND

(T1.CAL_YEAR = 2021) AND

(T1.CAL_MTH = 1) AND

(T1.FID = 000192) AND

(T1.L2_CD = 90) AND

(T1.L1_CD = 48) AND

(T2.INACTIVE_DT = DATE 2099-01-01)

GROUP BY

T1.L3_CD,

T1.L4_CD,

T1.L5_CD,

T1.CAT_EXT,

T1.OBJ,

T1.OCA,

T1.CAT

ORDER BY

T1.L3_CD,

T1.L4_CD,

T1.L5_CD,

T1.CAT_EXT,

T1.OBJ,

T1.OCA,

T1.CAT

FOR FETCH ONLY;

0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

Link to comment
Share on other sites

Hi Brad

If youve got a WHERE test on a column in the outer joined table, you might be ending up with an INNER join. I saw this in your SQL - T2.INACTIVE_DT = DATE 2099-01-01

Heres an old troubleshooting note about this behavior from a long time ago:

LEFT_OUTER JOIN does not behave as expected

Hope that helps!

Toby

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...