KB8019: When using SQL 89 outer join syntax, Informix processes the NVL function prior to joining which may be unexpected.

• Strategy

This article notes an issue with outer joins and Informix

Given the following tables:
TABLE_1

Join results; calculate Contribution and rank
select pa13.CATEGORY_ID CATEGORY_ID,
   a14.CATEGORY_DESC CATEGORY_DESC,
   pa11.SUBCAT_ID SUBCAT_ID,
   a12.SUBCAT_DESC SUBCAT_DESC,
   pa11.Revenue Revenue,
   pa13.WJXBFS1 WJXBFS1,
   ISNULL((pa11.Revenue / NULLIF(pa13.WJXBFS1, 0)), 0) WJXBFS2,
   rank () over (partition by pa13.CATEGORY_ID order by ISNULL((pa11.Revenue / NULLIF(pa13.WJXBFS1, 0)), 0) desc) WJXBFS3
into #ZZAM02
from #ZZMD00 pa11
   join LU_SUBCATEG a12
   on (pa11.SUBCAT_ID = a12.SUBCAT_ID)
   join #ZZMD01 pa13
   on (a12.CATEGORY_ID = pa13.CATEGORY_ID)
   join LU_CATEGORY a14
   on (pa13.CATEGORY_ID = a14.CATEGORY_ID)

Keep only top-ranking rows without affecting metric results
select pa11.CATEGORY_ID CATEGORY_ID,
   pa11.CATEGORY_DESC CATEGORY_DESC,
   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.SUBCAT_DESC SUBCAT_DESC,
   pa11.Revenue WJXBFS1,
   pa11.WJXBFS1 WJXBFS2,
   pa11.WJXBFS2 WJXBFS3
from #ZZAM02 pa11
where (pa11.WJXBFS3 <= 1.0)

drop table #ZZMD00
drop table #ZZMD01
drop table #ZZAM02

Best Practices ⟩

Authentication ⟩

Authorization ⟩

Encryption

Web Security ⟩

ID_1123ID_112SQL 92SQL 89

TABLE_2

A query with an outer join:

Join results; calculate Contribution and rank select pa13.CATEGORY_ID CATEGORY_ID, a14.CATEGORY_DESC CATEGORY_DESC, pa11.SUBCAT_ID SUBCAT_ID, a12.SUBCAT_DESC SUBCAT_DESC, pa11.Revenue Revenue, pa13.WJXBFS1 WJXBFS1, ISNULL((pa11.Revenue / NULLIF(pa13.WJXBFS1, 0)), 0) WJXBFS2, rank () over (partition by pa13.CATEGORY_ID order by ISNULL((pa11.Revenue / NULLIF(pa13.WJXBFS1, 0)), 0) desc) WJXBFS3 into #ZZAM02 from #ZZMD00 pa11 join LU_SUBCATEG a12 on (pa11.SUBCAT_ID = a12.SUBCAT_ID) join #ZZMD01 pa13 on (a12.CATEGORY_ID = pa13.CATEGORY_ID) join LU_CATEGORY a14 on (pa13.CATEGORY_ID = a14.CATEGORY_ID) Keep only top-ranking rows without affecting metric results select pa11.CATEGORY_ID CATEGORY_ID, pa11.CATEGORY_DESC CATEGORY_DESC, pa11.SUBCAT_ID SUBCAT_ID, pa11.SUBCAT_DESC SUBCAT_DESC, pa11.Revenue WJXBFS1, pa11.WJXBFS1 WJXBFS2, pa11.WJXBFS2 WJXBFS3 from #ZZAM02 pa11 where (pa11.WJXBFS3 <= 1.0) drop table #ZZMD00 drop table #ZZMD01 drop table #ZZAM02 Best Practices ⟩ Authentication ⟩ Authorization ⟩ Encryption Web Security ⟩ ID_1123ID_112SQL 92SQL 89	Join results; calculate Contribution and rank select pa13.CATEGORY_ID CATEGORY_ID, a14.CATEGORY_DESC CATEGORY_DESC, pa11.SUBCAT_ID SUBCAT_ID, a12.SUBCAT_DESC SUBCAT_DESC, pa11.Revenue Revenue, pa13.WJXBFS1 WJXBFS1, ISNULL((pa11.Revenue / NULLIF(pa13.WJXBFS1, 0)), 0) WJXBFS2, rank () over (partition by pa13.CATEGORY_ID order by ISNULL((pa11.Revenue / NULLIF(pa13.WJXBFS1, 0)), 0) desc) WJXBFS3 into #ZZAM02 from #ZZMD00 pa11 join LU_SUBCATEG a12 on (pa11.SUBCAT_ID = a12.SUBCAT_ID) join #ZZMD01 pa13 on (a12.CATEGORY_ID = pa13.CATEGORY_ID) join LU_CATEGORY a14 on (pa13.CATEGORY_ID = a14.CATEGORY_ID) Keep only top-ranking rows without affecting metric results select pa11.CATEGORY_ID CATEGORY_ID, pa11.CATEGORY_DESC CATEGORY_DESC, pa11.SUBCAT_ID SUBCAT_ID, pa11.SUBCAT_DESC SUBCAT_DESC, pa11.Revenue WJXBFS1, pa11.WJXBFS1 WJXBFS2, pa11.WJXBFS2 WJXBFS3 from #ZZAM02 pa11 where (pa11.WJXBFS3 <= 1.0) drop table #ZZMD00 drop table #ZZMD01 drop table #ZZAM02 Best Practices ⟩ Authentication ⟩ Authorization ⟩ Encryption Web Security ⟩ ID_1123ID_112SQL 92SQL 89
select a1.ID_1, a2.ID_1 from TABLE_1 a1 left outer join TABLE_2 a2 on a1.ID_1 = a2.ID_1 WHERE nvl(a1.ID_1, 0) + nvl(a2.ID_1,0) > 10	select a1.ID_1, a2.ID_1 from TABLE_1 a1, outer TABLE_2 a2 WHERE a1.ID_1 = a2.ID_1 and nvl(a1.ID_1, 0) + nvl(a2.ID_1,0) > 10 Returns:
Returns:	Join Type = SQL 92 (Correct)Join Type = SQL 89 (Incorrect)

The two queries should return the same data but they do not. Informix processes the [nvl(a1.ID_1, 0) + nvl(a2.ID_1,0)] operation prior to joining when using an outer clause in SQL 89. Only the results on the deficient table are filtered, not the complete rows.
In Strategy, when a metric qualification (MQ) or report limit is performed on a compound metric, this issue may affect the results retrieved by the Strategy Engine.
Given the following objects:
Metrics:

M1: Sum(Cost) {~+}
Condition: [Month] in (200001, 200002, 200003)
Metric Join Type: Inner

M2: Sum([Units Received]) {~+}
Condition: [Month] in (200001, 200002, 200003)
Metric Join Type: Outer
M3: M1+M2
Template:
[Month], [M1]
Report Limit:

Join Type = SQL 92 (Correct)Join Type = SQL 89 (Incorrect)	Join Type = SQL 92 (Correct)Join Type = SQL 89 (Incorrect)

select a11.MONTH_ID MONTH_ID, sum(a11.TOT_COST) WJXBFS1 from MNTH_CATEGORY_SLS a11 where a11.MONTH_ID in (200001, 200002, 200003) group by a11.MONTH_ID into temp MD000 with no log select a11.MONTH_ID MONTH_ID, sum(a11.UNITS_RECEIVED) WJXBFS1 from INVENTORY_ORDERS a11 where a11.MONTH_ID in (200001, 200002) group by a11.MONTH_ID into temp MD001 with no log select pa2.MONTH_ID MONTH_ID, a11.MONTH_DESC MONTH_DESC, (NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) WJXBFS1 from MD001 pa2 left outer join MD000 pa1 on (pa2.MONTH_ID = pa1.MONTH_ID) join LU_MONTH a11 on (pa2.MONTH_ID = a11.MONTH_ID) where ((NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) > 600000.0)	select a11.MONTH_ID MONTH_ID, sum(a11.TOT_COST) WJXBFS1 from MNTH_CATEGORY_SLS a11 where a11.MONTH_ID in (200001, 200002, 200003) group by a11.MONTH_ID into temp MD000 with no log select a11.MONTH_ID MONTH_ID, sum(a11.UNITS_RECEIVED) WJXBFS1 from INVENTORY_ORDERS a11 where a11.MONTH_ID in (200001, 200002) group by a11.MONTH_ID into temp MD001 with no log select pa2.MONTH_ID MONTH_ID, a11.MONTH_DESC MONTH_DESC, (NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) WJXBFS1 from MD001 pa2, outer MD000 pa1, LU_MONTH a11 where pa2.MONTH_ID = pa1.MONTH_ID and pa2.MONTH_ID = a11.MONTH_ID and ((NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) > 600000.0)

SOLUTION:

Use Join 92 for the query:

Comment

0 comments

Details

Knowledge Article

Published:

May 25, 2017

Last Updated:

May 25, 2017