'VBA ADODB : replace the missing value

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 code is:

    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

Solution 1:^[1]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1

'VBA ADODB : replace the missing value

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]