VBA ADODB : replace the missing value-CodePudding

I'm wondering how to replace the missing values (while left join).

I mean, if I didn't get a data after the join (ev4.D_EFFET is empty), I would like to put 'Not Applicable'. The NVL function doesn't work, I don't have the 'Not Applicable' as a result.

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

My full code is:

Public Sub Donnees_EA()
    Dim RECSET2 As New ADODB.Recordset
    Dim Num_Ligne As Long
    Dim Num_Ligne2 As Long

    Num_Ligne = Range("Chapeau").Row
    Police_donnee = Trim(UCase(Worksheets("DONNES_EA").Range("Police_donnee").Value))

    While Cells(Num_Ligne, Range("Colonne_4").Column) <> ""
        ActiveSheet.Cells(Num_Ligne, Range("Colonne_4").Column).EntireRow.Clear
        Num_Ligne = Num_Ligne   1
    Wend

    Call CONNEXION_PEGASE("xxx", "xxx", "xxx")

    ActiveSheet.Range("Colonne_1").Value = "Contrat"
    ActiveSheet.Range("Colonne_2").Value = "Support"
    ActiveSheet.Range("Colonne_3").Value = "Type"
    ActiveSheet.Range("Colonne_4").Value = "Frais de gestion"
    ActiveSheet.Range("Colonne_5").Value = "Taux de PAB net"
    ActiveSheet.Range("Colonne_6").Value = "Taux de bonus"
    ActiveSheet.Range("Colonne_7").Value = "Date mouvement"
    ActiveSheet.Range("Colonne_8").Value = "Montant mouvement"
    ActiveSheet.Range("Colonne_9").Value = "Motif mouvement"
    ActiveSheet.Range("Colonne_10").Value = "PM N-1 Pegase nette de fiscalité "
    ActiveSheet.Range("Colonne_11").Value = " PM N Pegase brut de fiscalité"
    ActiveSheet.Range("Colonne_12").Value = "Fiscalité"
    ActiveSheet.Range("Colonne_13").Value = "PM N Pegase nette de fiscalité"

    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

Wanted result :

My result :

CodePudding user response：

Your query wouldn't return any rows for cases where d_effet being NULL because you have the following condition:

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

The rows would be eliminated by this condition if D_EFFET is NULL.

Also, your LEFT JOIN to DB_EVENEMENT ev4 is turned into an inner join because of this condition; if you intended to limit this join to those rows with the above condition move the criteria to the ON clause.