VBA ADODB：替换缺失值

Question

我想知道如何替换缺失的值（在左连接时）。

我的意思是，如果我在连接后没有获取数据（ev4.D_EFFET 为空），我想输入'Not Applicable'。 NVL 函数不起作用，因此我没有得到'Not Applicable'。

NVL(TO_CHAR(ev4.D_EFFET), 'Not Applicable') as D_EF

我的代码是：

    RECSET2.Open " select ev1.NO_POLICE, abs(sum(ev1.MT_BRUT)) as Fiscalite, ev1.IS_DEVISE, sup.CD_SUPPORT, ev1.IS_SUPPORT," & _
    " ev2.TX_TAUX/100 as TAUX1,ev3.TX_TAUX/100 as TAUX2, srva1.MT_EA as MT_EA1, srva2.MT_EA as MT_EA2, (abs(sum(ev1.MT_BRUT))+srva2.MT_EA) as Brut_fis," & _
    " NVL(TO_CHAR(ev4.MT_BRUT),'Not Applicable') as MT, NVL(TO_CHAR(ev4.D_EFFET), 'Not Applicable') as D_EF,ev4.LP_NATUR_FLUX" & _
    " from DB_EVENEMENT ev1 left join DB_SUPPORT sup on ev1.IS_SUPPORT=sup.IS_SUPPORT" & _
    " left join DB_EVENEMENT ev2 on  ev1.NO_POLICE=ev2.NO_POLICE and ev1.IS_SUPPORT=ev2.IS_SUPPORT" & _
    " left join DB_EVENEMENT ev3 on  ev1.NO_POLICE=ev3.NO_POLICE and ev1.IS_SUPPORT=ev3.IS_SUPPORT" & _
    " left join SRVEA.DB_SEA_SUPPORT_HISTO srva1 on  ev1.NO_POLICE=srva1.NO_POLICE and ev1.IS_SUPPORT=srva1.IS_SUPPORT" & _
    " left join SRVEA.DB_SEA_SUPPORT_HISTO srva2 on  ev1.NO_POLICE=srva2.NO_POLICE and ev1.IS_SUPPORT=srva2.IS_SUPPORT" & _
    " left join  DB_EVENEMENT ev4 on  ev1.NO_POLICE=ev4.NO_POLICE and ev1.IS_SUPPORT=ev4.IS_SUPPORT " & _
    " where ev1.NO_POLICE='" & Police_donnee & "' and  ev1.IS_CLASSE_EVT=365" & _
    " and ev1.LP_STATUT_EVT='DONE' and extract(year from ev1.D_EFFET)=2021 and" & _
    " ev2.IS_CLASSE_EVT=563 and extract(year from ev2.D_EFFET)>2020" & _
    " and ev3.IS_CLASSE_EVT=121 and ev3.N_EXERCICE_CPTA in (2021) and ev3.LP_STATUT_EVT^='ANNUL'" & _
    " and extract(year from srva1.D_VALO)=2020" & _
    " and extract(month from srva1.D_VALO)=12  and extract(day from srva1.D_VALO)=31" & _
    " and srva1.S_TYPE_SUPPORT='TXGAR'" & _
    " and extract(year from srva2.D_VALO)=2021" & _
    " and extract(month from srva2.D_VALO)=12  and extract(day from srva2.D_VALO)=31" & _
    " and srva2.S_TYPE_SUPPORT='TXGAR'" & _
    " and srva2.S_TYPE_SUPPORT='TXGAR' and extract(year from ev4.d_effet)=2021 and ev4.IS_CLASSE_EVT=39" & _
    " group by ev1.NO_POLICE, ev1.IS_DEVISE, sup.CD_SUPPORT, ev1.IS_SUPPORT,ev2.TX_TAUX/100, ev3.TX_TAUX/100,srva1.MT_EA,srva2.MT_EA, ev4.MT_BRUT, ev4.D_EFFET, ev4.LP_NATUR_FLUX", cnn_Pegase, adOpenDynamic, adLockBatchOptimistic

    xlRow = Range("Colonne_1").Row + 1 + xlRow

    Do While Not RECSET2.EOF
        ActiveSheet.Cells(xlRow, Range("Colonne_1").Column).Value = RECSET2("NO_POLICE").Value
        ActiveSheet.Cells(xlRow, Range("Colonne_2").Column).Value = RECSET2("CD_SUPPORT").Value

        Select Case RECSET2.Fields("IS_DEVISE").Value
            Case 46
                ActiveSheet.Cells(xlRow, Range("Colonne_3").Column).Value = "EUR"
            Case Else
                ActiveSheet.Cells(xlRow, Range("Colonne_3").Column).Value = "UC"
        End Select

        ActiveSheet.Cells(xlRow, Range("Colonne_4").Column).Value = RECSET2("TAUX1").Value
        ActiveSheet.Cells(xlRow, Range("Colonne_4").Column).NumberFormat = "0.00%"
        ActiveSheet.Cells(xlRow, Range("Colonne_5").Column).Value = RECSET2("TAUX2").Value
        ActiveSheet.Cells(xlRow, Range("Colonne_5").Column).NumberFormat = "0.00%"
        ActiveSheet.Cells(xlRow, Range("Colonne_6").Column).Value = 0
        ActiveSheet.Cells(xlRow, Range("Colonne_6").Column).NumberFormat = "0.00%"
        ActiveSheet.Cells(xlRow, Range("Colonne_7").Column).Value = RECSET2.Fields("D_EF").Value
        ActiveSheet.Cells(xlRow, Range("Colonne_8").Column).Value = RECSET2("MT").Value
        ActiveSheet.Cells(xlRow, Range("Colonne_8").Column).NumberFormat = "#,##0.00€"
        ActiveSheet.Cells(xlRow, Range("Colonne_9").Column).Value = RECSET2("LP_NATUR_FLUX").Value
        ActiveSheet.Cells(xlRow, Range("Colonne_10").Column).Value = RECSET2("MT_EA1").Value
        ActiveSheet.Cells(xlRow, Range("Colonne_10").Column).NumberFormat = "#,##0.00€"
        ActiveSheet.Cells(xlRow, Range("Colonne_11").Column).Value = RECSET2("Brut_fis").Value
        ActiveSheet.Cells(xlRow, Range("Colonne_11").Column).NumberFormat = "#,##0.00€"
        ActiveSheet.Cells(xlRow, Range("Colonne_12").Column).Value = RECSET2("Fiscalite").Value
        ActiveSheet.Cells(xlRow, Range("Colonne_12").Column).NumberFormat = "#,##0.00€"
        ActiveSheet.Cells(xlRow, Range("Colonne_13").Column).Value = RECSET2("MT_EA2").Value
        ActiveSheet.Cells(xlRow, Range("Colonne_13").Column).NumberFormat = "#,##0.00€"

        RECSET2.MoveNext
        xlRow = xlRow + 1
    Loop

    RECSET2.Close
    Call DECONNEXION_PEGASE
End Sub

Answer 1

对于 d_effet 为 NULL 的情况，您的查询不会返回任何行，因为您有以下条件：

WHERE ...
and extract(year from ev4.d_effet)=2021 and ev4.IS_CLASSE_EVT=39

如果 D_EFFET 为 NULL，则此条件将消除这些行。

此外，由于这种情况，您的 LEFT JOIN 到 DB_EVENMENT ev4 会变成内部联接；如果您打算将此连接限制为具有上述条件的行，请将条件移至 ON 子句。