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

[Brad Gronli]

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

[Manoj Chaurasia]

HI Brad

Welcome to myibi. It is usually a great place to get answers to your development questions but in this case it sounds like you have found a bug and should open a case with techsupport. In the mean time I saw you posted your SQL trace but can you also post the WebFOCUS code as well.

[Brian Fasching]

Have you tried SET SHORTPATH = SQL

[Toby Mills]

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