CODEWITHSUNDEEP
vbaoracle-databasemissing-dataadodb
VBA_Anne_Marie
VBA_Anne_Marie

Reputation: 373

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

Upvotes: 1

Views: 122

Answers (1)

tinazmu
tinazmu

Reputation: 5139

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.

Upvotes: 1

Related Questions