[Acces 2003, VBA] "zelfde" query, verschillend resultaat

[Rene59]

Ik heb een tool in Access gemaakt, die queries uitvoert op een MS Sql server.
Eerst deed ik dit dmv een ODBC datasource (System DSN), met de volgende query:

code:

sSQL = "SELECT " & _
            "la_all.BRANCH_PLANT AS BP, " & _
            "la_all.SO_1_QTY AS [SO_" & aLAmonth(2) & "], la_all.SO_2_QTY AS [SO_" & aLAmonth(3) & "], la_all.SO_3_QTY AS [SO_" & aLAmonth(4) & "], " & _
            "la_all.BI_CUR_QTY AS [BI_" & aLAmonth(1) & "], la_all.BI_1_QTY AS [BI_" & aLAmonth(2) & "], la_all.BI_2_QTY AS [BI_" & aLAmonth(3) & "], la_all.BI_3_QTY AS [BI_" & aLAmonth(4) & "], " & _
            "(la_all.SO_1_QTY + la_all.SO_2_QTY + la_all.SO_3_QTY)/3 AS [SO_AVG 3], " & _
            "(la_all.SO_1_QTY + la_all.SO_2_QTY + la_all.SO_3_QTY + la_all.SO_4_QTY + la_all.SO_5_QTY + la_all.SO_6_QTY)/6 AS [SO_AVG 6], " & _
            "(la_all.BI_1_QTY + la_all.BI_2_QTY + la_all.BI_3_QTY)/3 AS [BI_AVG 3], " & _
            "(la_all.BI_1_QTY + la_all.BI_2_QTY + la_all.BI_3_QTY + la_all.BI_4_QTY + la_all.BI_5_QTY + la_all.BI_6_QTY)/6 AS [BI_AVG 6], " & _
            "(la_all.AA_1 + la_all.AA_2 + la_all.AA_3 + la_all.AA_4 + la_all.AA_5 + la_all.AA_6 + la_all.AA_7 + la_all.AA_8 + la_all.AA_9 + la_all.AA_10 + la_all.AA_11 + la_all.AA_12 + la_all.AA_13)/3 AS [AA_AVG 3], " & _
            "((la_04.FC04_1 + la_AF.FCAF_1) + (la_04.FC04_2 + la_AF.FCAF_2) + (la_04.FC04_3 + la_AF.FCAF_3) + (la_04.FC04_4 + la_AF.FCAF_4) + (la_04.FC04_5 + la_AF.FCAF_5) + (la_04.FC04_6 + la_AF.FCAF_6) + (la_04.FC04_7 + la_AF.FCAF_7) + (la_04.FC04_8 + la_AF.FCAF_8) + (la_04.FC04_9 + la_AF.FCAF_9) + (la_04.FC04_10 + la_AF.FCAF_10) + (la_04.FC04_11 + la_AF.FCAF_11) + (la_04.FC04_12 + la_AF.FCAF_12) + (la_04.FC04_13 + la_AF.FCAF_13))/3 AS [FC_AVG 3] " & _
        "FROM (" & _
            "(SELECT * FROM dbo.LOGISTIC_ANALYSIS_VW WHERE SHORT_ITEM_NO=" & iShort & ") la_all " & _
        "LEFT JOIN " & _
            "(SELECT SHORT_ITEM_NO, BRANCH_PLANT, IIF(FC_1 IS NULL, 0, FC_1) AS FC04_1, IIF(FC_2 IS NULL, 0, FC_2) AS FC04_2, IIF(FC_3 IS NULL, 0, FC_3) AS FC04_3, IIF(FC_4 IS NULL, 0, FC_4) AS FC04_4, IIF(FC_5 IS NULL, 0, FC_5) AS FC04_5, IIF(FC_6 IS NULL, 0, FC_6) AS FC04_6, IIF(FC_7 IS NULL, 0, FC_7) AS FC04_7, IIF(FC_8 IS NULL, 0, FC_8) AS FC04_8, IIF(FC_9 IS NULL, 0, FC_9) AS FC04_9, IIF(FC_10 IS NULL, 0, FC_10) AS FC04_10, IIF(FC_11 IS NULL, 0, FC_11) AS FC04_11, IIF(FC_12 IS NULL, 0, FC_12) AS FC04_12, IIF(FC_13 IS NULL, 0, FC_13) AS FC04_13 FROM dbo.LOGISTIC_ANALYSIS_VW WHERE SHORT_ITEM_NO=" & iShort & " AND FORECAST_METHOD_CD='04') la_04 ON la_all.SHORT_ITEM_NO = la_04.SHORT_ITEM_NO AND la_all.BRANCH_PLANT = la_04.BRANCH_PLANT) " & _
        "LEFT JOIN " & _
            "(SELECT SHORT_ITEM_NO, BRANCH_PLANT, IIF(FC_1 IS NULL, 0, FC_1) AS FCAF_1, IIF(FC_2 IS NULL, 0, FC_2) AS FCAF_2, IIF(FC_3 IS NULL, 0, FC_3) AS FCAF_3, IIF(FC_4 IS NULL, 0, FC_4) AS FCAF_4, IIF(FC_5 IS NULL, 0, FC_5) AS FCAF_5, IIF(FC_6 IS NULL, 0, FC_6) AS FCAF_6, IIF(FC_7 IS NULL, 0, FC_7) AS FCAF_7, IIF(FC_8 IS NULL, 0, FC_8) AS FCAF_8, IIF(FC_9 IS NULL, 0, FC_9) AS FCAF_9, IIF(FC_10 IS NULL, 0, FC_10) AS FCAF_10, IIF(FC_11 IS NULL, 0, FC_11) AS FCAF_11, IIF(FC_12 IS NULL, 0, FC_12) AS FCAF_12, IIF(FC_13 IS NULL, 0, FC_13) AS FCAF_13 FROM dbo.LOGISTIC_ANALYSIS_VW WHERE SHORT_ITEM_NO=" & iShort & " AND FORECAST_METHOD_CD='AF') la_AF ON la_all.SHORT_ITEM_NO = la_AF.SHORT_ITEM_NO AND la_all.BRANCH_PLANT = la_AF.BRANCH_PLANT " & _
        "WHERE NOT " & _
            "(la_all.SO_1_QTY = 0 AND la_all.SO_2_QTY = 0 AND la_all.SO_3_QTY = 0 AND la_all.SO_4_QTY = 0 AND la_all.SO_5_QTY = 0 AND la_all.SO_6_QTY = 0 AND la_all.BI_CUR_QTY = 0 AND la_all.BI_1_QTY = 0 AND la_all.BI_2_QTY = 0 AND la_all.BI_3_QTY = 0 AND la_all.BI_4_QTY = 0 AND la_all.BI_5_QTY = 0 AND la_all.BI_6_QTY = 0) " & _
        "GROUP BY " & _
            "la_all.BRANCH_PLANT, la_all.SO_1_QTY, la_all.SO_2_QTY, la_all.SO_3_QTY, la_all.SO_4_QTY, la_all.SO_5_QTY, la_all.SO_6_QTY, la_all.BI_CUR_QTY, la_all.BI_1_QTY, la_all.BI_2_QTY, la_all.BI_3_QTY, la_all.BI_4_QTY, la_all.BI_5_QTY, la_all.BI_6_QTY, la_all.AA_1, la_all.AA_2, la_all.AA_3, la_all.AA_4, la_all.AA_5, la_all.AA_6, la_all.AA_7, la_all.AA_8, la_all.AA_9, la_all.AA_10, la_all.AA_11, la_all.AA_12, la_all.AA_13, " & _
            "la_04.FC04_1, la_AF.FCAF_1, la_04.FC04_2, la_AF.FCAF_2, la_04.FC04_3, la_AF.FCAF_3, la_04.FC04_4, la_AF.FCAF_4, la_04.FC04_5, la_AF.FCAF_5, la_04.FC04_6, la_AF.FCAF_6, la_04.FC04_7, la_AF.FCAF_7, la_04.FC04_8, la_AF.FCAF_8, la_04.FC04_9, la_AF.FCAF_9, la_04.FC04_10, la_AF.FCAF_10, la_04.FC04_11, la_AF.FCAF_11, la_04.FC04_12, la_AF.FCAF_12, la_04.FC04_13, la_AF.FCAF_13 " & _
        "ORDER BY " & _
            "la_all.BRANCH_PLANT"

Nu ben ik de tool aan het aanpassen, zodat er geen System DSN meer nodig is en hij direct verbinding legt met de SQL Server. Probleem waar ik tegenaan liep is dat IIF blijkbaar niet meer werkt op die manier, en dat je CASE moet gebruiken.
Ik heb de query aangepast naar deze:

code:

sSQL = "SELECT " & _
            "la_all.BRANCH_PLANT AS BP, " & _
            "la_all.SO_1_QTY AS [SO_" & aLAmonth(2) & "], la_all.SO_2_QTY AS [SO_" & aLAmonth(3) & "], la_all.SO_3_QTY AS [SO_" & aLAmonth(4) & "], " & _
            "la_all.BI_CUR_QTY AS [BI_" & aLAmonth(1) & "], la_all.BI_1_QTY AS [BI_" & aLAmonth(2) & "], la_all.BI_2_QTY AS [BI_" & aLAmonth(3) & "], la_all.BI_3_QTY AS [BI_" & aLAmonth(4) & "], " & _
            "(la_all.SO_1_QTY + la_all.SO_2_QTY + la_all.SO_3_QTY)/3 AS [SO_AVG 3], " & _
            "(la_all.SO_1_QTY + la_all.SO_2_QTY + la_all.SO_3_QTY + la_all.SO_4_QTY + la_all.SO_5_QTY + la_all.SO_6_QTY)/6 AS [SO_AVG 6], " & _
            "(la_all.BI_1_QTY + la_all.BI_2_QTY + la_all.BI_3_QTY)/3 AS [BI_AVG 3], " & _
            "(la_all.BI_1_QTY + la_all.BI_2_QTY + la_all.BI_3_QTY + la_all.BI_4_QTY + la_all.BI_5_QTY + la_all.BI_6_QTY)/6 AS [BI_AVG 6], " & _
            "(la_all.AA_1 + la_all.AA_2 + la_all.AA_3 + la_all.AA_4 + la_all.AA_5 + la_all.AA_6 + la_all.AA_7 + la_all.AA_8 + la_all.AA_9 + la_all.AA_10 + la_all.AA_11 + la_all.AA_12 + la_all.AA_13)/3 AS [AA_AVG 3], " & _
            "((la_04.FC04_1 + la_AF.FCAF_1) + (la_04.FC04_2 + la_AF.FCAF_2) + (la_04.FC04_3 + la_AF.FCAF_3) + (la_04.FC04_4 + la_AF.FCAF_4) + (la_04.FC04_5 + la_AF.FCAF_5) + (la_04.FC04_6 + la_AF.FCAF_6) + (la_04.FC04_7 + la_AF.FCAF_7) + (la_04.FC04_8 + la_AF.FCAF_8) + (la_04.FC04_9 + la_AF.FCAF_9) + (la_04.FC04_10 + la_AF.FCAF_10) + (la_04.FC04_11 + la_AF.FCAF_11) + (la_04.FC04_12 + la_AF.FCAF_12) + (la_04.FC04_13 + la_AF.FCAF_13))/3 AS [FC_AVG 3] " & _
        "FROM ( " & _
            "(SELECT * FROM dbo.LOGISTIC_ANALYSIS_VW WHERE SHORT_ITEM_NO=" & iShort & ") la_all " & _
        "Left Join " & _
            "(SELECT SHORT_ITEM_NO, BRANCH_PLANT, CASE WHEN FC_1 IS NULL THEN 0 ELSE FC_1 END AS FC04_1, CASE WHEN FC_2 IS NULL THEN 0 ELSE FC_2 END AS FC04_2, CASE WHEN FC_3 IS NULL THEN 0 ELSE FC_3 END AS FC04_3, CASE WHEN FC_4 IS NULL THEN 0 ELSE FC_4 END AS FC04_4, CASE WHEN FC_5 IS NULL THEN 0 ELSE FC_5 END AS FC04_5, CASE WHEN FC_6 IS NULL THEN 0 ELSE FC_6 END AS FC04_6, CASE WHEN FC_7 IS NULL THEN 0 ELSE FC_7 END AS FC04_7, CASE WHEN FC_8 IS NULL THEN 0 ELSE FC_8 END AS FC04_8, CASE WHEN FC_9 IS NULL THEN 0 ELSE FC_9 END AS FC04_9, CASE WHEN FC_10 IS NULL THEN 0 ELSE FC_10 END AS FC04_10, CASE WHEN FC_11 IS NULL THEN 0 ELSE FC_11 END AS FC04_11, CASE WHEN FC_12 IS NULL THEN 0 ELSE FC_12 END AS FC04_12, CASE WHEN FC_13 IS NULL THEN 0 ELSE FC_13 END AS FC04_13 FROM dbo.LOGISTIC_ANALYSIS_VW WHERE SHORT_ITEM_NO=" & iShort & " AND FORECAST_METHOD_CD='04') la_04 ON la_all.SHORT_ITEM_NO = la_04.SHORT_ITEM_NO AND la_all.BRANCH_PLANT = la_04.BRANCH_PLANT) " & _
        "Left Join " & _
            "(SELECT SHORT_ITEM_NO, BRANCH_PLANT, CASE WHEN FC_1 IS NULL THEN 0 ELSE FC_1 END AS FCAF_1, CASE WHEN FC_2 IS NULL THEN 0 ELSE FC_2 END AS FCAF_2, CASE WHEN FC_3 IS NULL THEN 0 ELSE FC_3 END AS FCAF_3, CASE WHEN FC_4 IS NULL THEN 0 ELSE FC_4 END AS FCAF_4, CASE WHEN FC_5 IS NULL THEN 0 ELSE FC_5 END AS FCAF_5, CASE WHEN FC_6 IS NULL THEN 0 ELSE FC_6 END AS FCAF_6, CASE WHEN FC_7 IS NULL THEN 0 ELSE FC_7 END AS FCAF_7, CASE WHEN FC_8 IS NULL THEN 0 ELSE FC_8 END AS FCAF_8, CASE WHEN FC_9 IS NULL THEN 0 ELSE FC_9 END AS FCAF_9, CASE WHEN FC_10 IS NULL THEN 0 ELSE FC_10 END AS FCAF_10, CASE WHEN FC_11 IS NULL THEN 0 ELSE FC_11 END AS FCAF_11, CASE WHEN FC_12 IS NULL THEN 0 ELSE FC_12 END AS FCAF_12, CASE WHEN FC_13 IS NULL THEN 0 ELSE FC_13 END AS FCAF_13 FROM dbo.LOGISTIC_ANALYSIS_VW WHERE SHORT_ITEM_NO=" & iShort & " AND FORECAST_METHOD_CD='AF') la_AF ON la_all.SHORT_ITEM_NO = la_AF.SHORT_ITEM_NO AND la_all.BRANCH_PLANT = la_AF.BRANCH_PLANT " & _
        "WHERE NOT " & _
            "(la_all.SO_1_QTY = 0 AND la_all.SO_2_QTY = 0 AND la_all.SO_3_QTY = 0 AND la_all.SO_4_QTY = 0 AND la_all.SO_5_QTY = 0 AND la_all.SO_6_QTY = 0 AND la_all.BI_CUR_QTY = 0 AND la_all.BI_1_QTY = 0 AND la_all.BI_2_QTY = 0 AND la_all.BI_3_QTY = 0 AND la_all.BI_4_QTY = 0 AND la_all.BI_5_QTY = 0 AND la_all.BI_6_QTY = 0) " & _
        "Group BY " & _
            "la_all.BRANCH_PLANT, la_all.SO_1_QTY, la_all.SO_2_QTY, la_all.SO_3_QTY, la_all.SO_4_QTY, la_all.SO_5_QTY, la_all.SO_6_QTY, la_all.BI_CUR_QTY, la_all.BI_1_QTY, la_all.BI_2_QTY, la_all.BI_3_QTY, la_all.BI_4_QTY, la_all.BI_5_QTY, la_all.BI_6_QTY, la_all.AA_1, la_all.AA_2, la_all.AA_3, la_all.AA_4, la_all.AA_5, la_all.AA_6, la_all.AA_7, la_all.AA_8, la_all.AA_9, la_all.AA_10, la_all.AA_11, la_all.AA_12, la_all.AA_13, " & _
            "la_04.FC04_1 , la_AF.FCAF_1, la_04.FC04_2, la_AF.FCAF_2, la_04.FC04_3, la_AF.FCAF_3, la_04.FC04_4, la_AF.FCAF_4, la_04.FC04_5, la_AF.FCAF_5, la_04.FC04_6, la_AF.FCAF_6, la_04.FC04_7, la_AF.FCAF_7, la_04.FC04_8, la_AF.FCAF_8, la_04.FC04_9, la_AF.FCAF_9, la_04.FC04_10, la_AF.FCAF_10, la_04.FC04_11, la_AF.FCAF_11, la_04.FC04_12, la_AF.FCAF_12, la_04.FC04_13, la_AF.FCAF_13 " & _
        "Order BY " & _
            "la_all.BRANCH_PLANT"

Heb dus alle IIF dus vervangen door CASE expressies.
Het vreemde is nu dat ik in de laatste kolom van de resultaten een verschil zie. [FC_AVG 3] (regel 10) is in de nieuwe situatie voor meerdere records Null, terwijl in de oude situatie hier gewoon getallen stonden.
Als ik de FC_ waarden los opvraag en bereken dan blijkt de oude situatie correct te zijn en de nieuwe niet.

Is er iets aan de CASE expressie dat anders werkt dan de IIF expressie? Of doe ik iets anders fout?

[BertS]

In dit geval kun je gewoon ISNULL(<veld>,0) gebruiken.

[Rene59]

Beter laat dan nooit, bedankt voor je tip, het werkt nu