Microsoft 365 First and Last en ADODB VBA

fanch55 à dit:

Oracle est devenu bien strict !!!!
Terminer Requete avec un ;ça

Cliquez pour agrandir...

MarieParis à dit:

ça marche pas 🙁
Regarde la pièce jointe 1115257

Cliquez pour agrandir...

Sub testMP()
Dim Requete As String
    db_Ev = "db_evenement"
    db_Cl = "dp_classe_evt"
    no_police = "T342GB00827"
    Requete = _
        " Select  max(d_effet),sum(Abs(mt_brut_cie)) " & _
        "           from " & db_Ev & " as T1 " & _
        "     Inner join " & db_Cl & " as T2 " & _
        "     On T1.is_classe_evt = T2.is_classe_evt" & _
        "     where T1.no_police = '" & no_police & "' and T2.b_ea =1 and T2.b_rachat = 1 " & _
        "   and T1.d_effet = ( " & _
        "    select Max(d_effet) " & _
        "     from       " & db_Ev & "  ev " & _
        "     Inner join " & db_Cl & " cl " & _
        "     On ev.is_classe_evt=cl.is_classe_evt" & _
        "     where ev.no_police = '" & no_police & "' and cl.b_ea =1 and cl.b_rachat = 1 " & _
        "    ;); "
    RECSET.Open Requete, cnn_Pegase, adOpenDynamic, adLockBatchOptimistic

End Sub

fanch55 à dit:

Vraiment très strict : un select doit finir par un ;
et il y en a 2 ...

VB:

Sub testMP()
Dim Requete As String
    db_Ev = "db_evenement"
    db_Cl = "dp_classe_evt"
    no_police = "T342GB00827"
    Requete = _
        " Select  max(d_effet),sum(Abs(mt_brut_cie)) " & _
        "           from " & db_Ev & " as T1 " & _
        "     Inner join " & db_Cl & " as T2 " & _
        "     On T1.is_classe_evt = T2.is_classe_evt" & _
        "     where T1.no_police = '" & no_police & "' and T2.b_ea =1 and T2.b_rachat = 1 " & _
        "   and T1.d_effet = ( " & _
        "    select Max(d_effet) " & _
        "     from       " & db_Ev & "  ev " & _
        "     Inner join " & db_Cl & " cl " & _
        "     On ev.is_classe_evt=cl.is_classe_evt" & _
        "     where ev.no_police = '" & no_police & "' and cl.b_ea =1 and cl.b_rachat = 1 " & _
        "    ;); "
    RECSET.Open Requete, cnn_Pegase, adOpenDynamic, adLockBatchOptimistic

End Sub

Cliquez pour agrandir...

fanch55 à dit:

Vraiment très strict : un select doit finir par un ;
et il y en a 2 ...

VB:

Sub testMP()
Dim Requete As String
    db_Ev = "db_evenement"
    db_Cl = "dp_classe_evt"
    no_police = "T342GB00827"
    Requete = _
        " Select  max(d_effet),sum(Abs(mt_brut_cie)) " & _
        "           from " & db_Ev & " as T1 " & _
        "     Inner join " & db_Cl & " as T2 " & _
        "     On T1.is_classe_evt = T2.is_classe_evt" & _
        "     where T1.no_police = '" & no_police & "' and T2.b_ea =1 and T2.b_rachat = 1 " & _
        "   and T1.d_effet = ( " & _
        "    select Max(d_effet) " & _
        "     from       " & db_Ev & "  ev " & _
        "     Inner join " & db_Cl & " cl " & _
        "     On ev.is_classe_evt=cl.is_classe_evt" & _
        "     where ev.no_police = '" & no_police & "' and cl.b_ea =1 and cl.b_rachat = 1 " & _
        "    ;); "
    RECSET.Open Requete, cnn_Pegase, adOpenDynamic, adLockBatchOptimistic

End Sub

Cliquez pour agrandir...

MarieParis à dit:

J'ai rempla

Merci, mais , malheureusement ça ne marche pas :
Regarde la pièce jointe 1115260

Cliquez pour agrandir...

fanch55 à dit:

Essayez cela :

VB:

        " Select  Max(d_effet),Sum(Abs(mt_brut_cie)) " & _
        "           from " & db_Ev & " T1 " & _
        "     Inner join " & db_Cl & " T2 " & _
        "     On T1.is_classe_evt = T2.is_classe_evt" & _
        "     where T1.no_police = '" & no_police & "' and T2.b_ea = 1 and T2.b_rachat = 1 " & _
        "    and T1.d_effet = ( " & _
        "       select Max(d_effet)" & _
        "           from       " & db_Ev & " ev " & _
        "           Inner join " & db_Cl & " cl " & _
        "           On ev.is_classe_evt = cl.is_classe_evt" & _
        "       where ev.no_police= '" & no_police & "' and cl.b_ea = 1 and cl.b_rachat = 1 " & _
        "     )"

Cliquez pour agrandir...

MarieParis à dit:

Merci beaucoup, ça marche bien ! C'était quoi, l'erreur ?

Cliquez pour agrandir...

fanch55 à dit:

Oracle oh désespoir ....
Je me suis rappelé que contrairement à d'autres SGBD,
l'alias d'une table dans Oracle ne peut pas être écrit avec le mot_clé As 😡

Cliquez pour agrandir...