SQL query in VBA geeft foutmelding

[WillemSchaaper]

Hallo aan allen

Ik heb het volgende probleem. Ik probeer d.m.v. VBA data op te halen uit een mainframe. Hierbij krijg ik de foutmelding "typen komen niet met elkaar overeen" en de code stopt op de commandtext array. Als ik de query draai met QMF for windows, dan wordt de data foutloos opgehaald.

De code die gebruik in VBA ziet er zo uit.

With ActiveSheet.QueryTables.Add _
        (Connection:=Array _
            (Array("ODBC;DSN=DB2A30;UID=@J610;pwd=dsimed3;" & "MODE=SHARE;DBALIAS=DB2A30;SCHEMALIST='P';" _
                & "TABLETYPE='TABLE','VIEW','INOPERATIVE VIEW';LONGDATACOMPAT=1;DIS"), _
                Array("ABLEUNICODE=1;DISABLEKEYSETCURSOR=1;" & _
                 "PATCH1=1024;PATCH2=6;LOBMAXCOLUMNSIZE=1048575;")), Destination:=Range("A1"))



    .CommandText = Array _
    ("SELECT DATE, C_HERKOMST_KOWA AS HERKOMST," _
                & "IND_CPS_COLLO AS BAD_HEV," _
                & "IND_GEW_BRUTO_COL AS BRUTTO," _
                & "IND_GEW_NETTO_COL AS NETTO," _
                & "IND_GEW_TARRA_COL AS TARRA," _
                & "CASE WHEN SUBSTR(OPLTR_IVV, 1, 1) = SUBSTR(OPLTR_IVV, 2, 1) THEN 'V' ELSE 'K' END AS BEST" _
                & "FROM @vp0.P91T10" _
                & "WHERE DATE BETWEEN '2009-11-10' AND '2009-11-23'" _
                & "AND RECSRT IN ('10051' , '10054')" _
                & "UNION ALL" _
                & "SELECT DATE," _
                & "IND_C_HERKOMST AS HERKOMST," _
                & "IND_CPS_COLLO AS BAD_HEV," _
                & "IND_GEW_BRUTO_COL AS BRUTO," _
                & "IND_GEW_BRUTO_COL - IND_GEW_TARRA_COL AS NETTO," _
                & "IND_GEW_TARRA_COL AS TARRA," _
                & "CASE WHEN SUBSTR(IND_OPLTR_IVV_OUD, 1, 1) = SUBSTR(IND_OPLTR_IVV_OUD, 2, 1) THEN 'V' ELSE 'K' END AS BEST" _
                & "FROM @vp0.P91T14" _
                & "WHERE DATE BETWEEN '2009-11-10' AND '2009-11-23'" _
                & "AND RECSRT IN ('10051' , '10054')")
                
        .Name = "Query from DB2A30"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
                
    End With

De regel met CASE wordt in deze editor afgebroken, maar loopt in de VBA editor door tot AS BEST

Ik hoop dat iemand me verder kan helpen.

 

[snb]

Ik zou het hiermee proberen:

With ActiveSheet.QueryTables.Add("ODBC;DSN=DB2A30;UID=@J610;pwd=dsimed3;MODE=SHARE;DBALIAS=DB2A30;SCHEMALIST='P';TABLETYPE='TABLE','VIEW','INOPERATIVE VIEW';LONGDATACOMPAT=1;DISABLEUNICODE=1;DISABLEKEYSETCURSOR=1;PATCH1=1024;PATCH2=6;LOBMAXCOLUMNSIZE=1048575;", Range("A1"))
    .CommandText = "SELECT DATE, C_HERKOMST_KOWA AS HERKOMST,IND_CPS_COLLO AS BAD_HEV,IND_GEW_BRUTO_COL AS BRUTTO,IND_GEW_NETTO_COL AS NETTO,IND_GEW_TARRA_COL AS TARRA,CASE WHEN SUBSTR(OPLTR_IVV, 1, 1) = SUBSTR(OPLTR_IVV, 2, 1) THEN 'V' ELSE 'K' END AS BEST FROM @vp0.P91T10 WHERE DATE BETWEEN '2009-11-10' AND '2009-11-23' AND RECSRT IN ('10051' , '10054') UNION ALL SELECT DATE,IND_C_HERKOMST AS HERKOMST,IND_CPS_COLLO AS BAD_HEV,IND_GEW_BRUTO_COL AS BRUTO,IND_GEW_BRUTO_COL - IND_GEW_TARRA_COL AS NETTO,IND_GEW_TARRA_COL AS TARRA,CASE WHEN SUBSTR(IND_OPLTR_IVV_OUD, 1, 1) = SUBSTR(IND_OPLTR_IVV_OUD, 2, 1) THEN 'V' ELSE 'K' END AS BEST FROM @vp0.P91T14 WHERE DATE BETWEEN '2009-11-10' AND '2009-11-23' AND RECSRT IN ('10051' , '10054')"
    .Name = "Query from DB2A30"
    .Refresh False
End With

 

[WillemSchaaper]

Alvast bedank voor je antwoord. Ik ga het a.s. Maandag gelijk proberen.

Je hoort van me als het werkt

 

[WillemSchaaper]

SNB Het werkt.

Ik heb wel wat aanpassingen gedaan i.v.m. de leesbaarheid in de editor.

.CommandText = "SELECT DATE AS DATUM, C_HERKOMST_KOWA AS HERKOMST,IND_CPS_COLLO AS BAD_HEV" _
                & Chr(13) & Chr(10) & ",IND_GEW_BRUTO_COL AS BRUTO,IND_GEW_NETTO_COL AS NETTO" _
                & Chr(13) & Chr(10) & ",IND_GEW_TARRA_COL AS TARRA" _
                & Chr(13) & Chr(10) & ",CASE WHEN SUBSTR(OPLTR_IVV, 1, 1) = " _
                & Chr(13) & Chr(10) & "SUBSTR(OPLTR_IVV, 2, 1) THEN 'V' ELSE 'K' END AS BEST" _
                & Chr(13) & Chr(10) & "FROM @vp0.P91T10 WHERE DATE BETWEEN '2009-11-10' AND '2009-11-23'" _
                & Chr(13) & Chr(10) & "AND RECSRT IN ('10051' , '10054')" _
                & Chr(13) & Chr(10) & "UNION ALL" _
                & Chr(13) & Chr(10) & "SELECT DATE AS DATUM,IND_C_HERKOMST AS HERKOMST,IND_CPS_COLLO AS BAD_HEV" _
                & Chr(13) & Chr(10) & ",IND_GEW_BRUTO_COL AS BRUTO,IND_GEW_BRUTO_COL - IND_GEW_TARRA_COL AS NETTO" _
                & Chr(13) & Chr(10) & ",IND_GEW_TARRA_COL AS TARRA" _
                & Chr(13) & Chr(10) & ",CASE WHEN SUBSTR(IND_OPLTR_IVV_OUD, 1, 1) = " _
                & Chr(13) & Chr(10) & "SUBSTR(IND_OPLTR_IVV_OUD, 2, 1) THEN 'V' ELSE 'K' END AS BEST" _
                & Chr(13) & Chr(10) & "FROM @vp0.P91T14 WHERE DATE BETWEEN '2009-11-10' AND '2009-11-23'" _
                & Chr(13) & Chr(10) & "AND RECSRT IN ('10051' , '10054')"

Hartelijk bedankt voor je hulp.

Prettige feestdagen en een goede jaarwisseling.