ADODB : if in sql clause

You're looking for CASE WHEN...THEN...END; take your if condition and move it to a WHEN branch, like this:

,CASE WHEN cl.CD_EVT IN ('COPVT','XCOPVT','3 COPVT','COCVT','XCOCVT','3 COCVT') THEN 'OUI' 
      ELSE 'NON' 
 END AS VENTE

Make sure the quotes are consistently 'single quotes', too; you only need to use "double quotes" to delimit the VBA string literal.

The SQL would be much easier to debug if you concatenated it in a separate instruction than where you're sending it to the connection:

Dim sql As String
sql = "SELECT .... FROM .... INNER JOIN .... WHERE ...."

Debug.Print sql '<< print the actual concatenated query string, 
   'then you can copy to your favorite SQL client, debug it with an actual SQL editor, 
   'and then come back here and fix the string accordingly!

RECSET2.Open sql, ...

Editing/debugging SQL in a SQL editor is much easier than doing it in a VBA string literal:

SELECT
    dossier.NO_POLICE, 
    ev1.D_EFFET, 
    ev1.ID_FAMILLE_PORTEF, 
    ev1.ID_PORTEFEUILLE, 
    gr.LB_COURT_GR_EVT, 
    pers1.S_PRENOM||' '||pers1.S_NOM as Collaborateur, 
    proto.CD_PROTOCOLE, 
    ev1.ID_FAMILLE_PORTEF,
    comm.L_COMMENT_DOSSIER,
    dossier.UI_CREATION, 
    ev1.LP_STATUT_EVT, 
    sum(ev3.MT_BRUT) as Ecart, 
    ev1.MT_BRUT, 
-- this is where the problem is:
    cl.CD_EVT(case when cl.CD_EVT in ('COPVT','XCOPVT','3 COPVT','COCVT','XCOCVT','3 COCVT') then 'OUI' else 'NON' end) as VENTE,
    tiers2.CD_TIERS as Tmandataire, 
    pers3.S_RAISONSOC as Mandataire,
    tiers1.CD_TIERS as Tdepositaire, 
    pers2.S_RAISONSOC as Depositaire, 
    ev1.IS_EVENEMENT 
FROM DB_DOSSIER dossier 
left join DB_EVENEMENT ev1 on dossier.IS_DOSSIER = ev1.IS_DOSSIER  
left join DB_EVENEMENT ev2 on ev1.IS_EVENEMENT=ev2.IS_EVENEMENT_PERE 
left join DR_LIEN_EVT drevl on ev2.IS_EVENEMENT=drevl.IS_EVENEMENT 
left join DB_EVENEMENT ev3 on drevl.IS_EVT_LIE=ev3.IS_EVENEMENT
left join DP_CLASSE_EVT cl on ev1.IS_CLASSE_EVT=cl.IS_CLASSE_EVT
left join DP_GROUPE_EVT gr on cl.IS_GR_EVT=gr.IS_GR_EVT 
left join DB_COMMENT_DOSSIER comm on dossier.IS_DOSSIER=comm.IS_DOSSIER 
left join DR_COLLABORATEUR_PROTOCOLE collabproto on dossier.IS_PROTOCOLE=collabproto.IS_PROTOCOLE 
left join DB_COLLABORATEUR collab on collabproto.IS_COLLABORATEUR=collab.IS_COLLABORATEUR 
left join DB_PERSONNE pers1 on collab.IS_PERSONNE=pers1.IS_PERSONNE 
left join DB_PROTOCOLE proto on dossier.IS_PROTOCOLE=proto.IS_PROTOCOLE 
left join DB_PORTEFEUILLE portef1 on ev1.ID_FAMILLE_PORTEF=portef1.ID_FAMILLE_PORTEF and  ev1.ID_PORTEFEUILLE=portef1.ID_PORTEFEUILLE 
left join DB_TIERS tiers1 on tiers1.IS_TIERS=portef1.IS_TIERS_DEPOSITAIRE
left join DB_PERSONNE pers2 on tiers1.IS_PERSONNE=pers2.IS_PERSONNE 
left join DB_TIERS tiers2 on tiers2.IS_TIERS=portef1.IS_TIERS_GESTIONNAIRE 
left join DB_PERSONNE pers3 on tiers2.IS_PERSONNE=pers3.IS_PERSONNE 
WHERE
    dossier.CD_DOSSIER in ('COROP','COROC') 
    and dossier.LP_ETAT_DOSS not in ('CLOSE','ANNUL','A30') 
    and ev1.D_EFFET>=@date and ev1.IS_EVENEMENT_PERE is null 
GROUP BY
    dossier.NO_POLICE, 
    ev1.D_EFFET, 
    ev1.ID_FAMILLE_PORTEF, 
    ev1.ID_PORTEFEUILLE, 
    gr.LB_COURT_GR_EVT, 
    pers1.S_PRENOM, 
    pers1.S_NOM, 
    proto.CD_PROTOCOLE, 
    ev1.ID_FAMILLE_PORTEF,
    comm.L_COMMENT_DOSSIER,
    dossier.UI_CREATION, 
    ev1.LP_STATUT_EVT, 
    ev1.MT_BRUT,cl.CD_EVT, 
    tiers2.CD_TIERS, 
    pers3.S_RAISONSOC,
    tiers1.CD_TIERS, 
    pers2.S_RAISONSOC, 
    ev1.IS_EVENEMENT

Side note, that's a lot of LEFT JOIN; if any of them can be changed to INNER JOIN, consider doing it, it should improve query performance a bit.

So the problem is here:

    cl.CD_EVT(case when cl.CD_EVT in ('COPVT','XCOPVT','3 COPVT','COCVT','XCOCVT','3 COCVT') then 'OUI' else 'NON' end) as VENTE,

That should read:

    case when cl.CD_EVT in ('COPVT','XCOPVT','3 COPVT','COCVT','XCOCVT','3 COCVT') then 'OUI' else 'NON' end) as VENTE,

And then I expect the GROUP BY clause to be complaining about cl.CD_EVT being missing; make sure the GROUP BY clause includes all non-aggregated columns in the SELECT clause.

To follow-up on @MathieuGuidon's correct answer, since you need to create a new column with CASE, situate the CASE statement in a comma-separated statement under SELECT clause. Your above attempt conflated the two columns with missing comma between original column (cl.CD_EVT) and calculated column (VENTE) and therefore raised the Oracle syntax error.

SELECT
   ...
   , cl.CD_EVT        -- RETURN TABLE COLUMN
   , CASE WHEN cl.CD_EVT IN ('COPVT','XCOPVT','3 COPVT','COCVT','XCOCVT','3 COCVT') 
          THEN 'OUI' 
          ELSE 'NON'
     END AS VENTE     -- RETURN CALCULATED COLUMN
   , ...               

Additionally, for long SQL queries, consider saving all the text in an .sql file with all line break and space formatting for readability and maintainability. And to exercise parameterization, include needed qmark placeholders, ?, (without quotes) for any values to be binded to query. Then, read the contents into VBA and using ADO Command object, run parameeterized query. In doing so, you entirely separate SQL and VBA.

SQL (save as an .sql file with ? placeholders)

SELECT 
    ... -- WITH CORRECTED CASE STATEMENT SYNTAX FOR CALCULATED COLUMN
FROM ...

LEFT JOIN ...

WHERE
    dossier.CD_DOSSIER IN ('COROP','COROC') 
    AND dossier.LP_ETAT_DOSS NOT IN ('CLOSE','ANNUL','A30') 
    AND ev1.D_EFFET >= ?    -- QMARK PARAM PLACEHOLDER
    AND ev1.IS_EVENEMENT_PERE IS NULL

GROUP BY 
    ...

VBA (read in .sql and run parameterized query)

Sub RunSQLQuery()
    Dim cnn_Pegase As ADODB.Connection
    Dim RECSET2 As ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim strSQL As String

    Set cnn_Pegase = New ADODB.Connection 
    cnn_Pegase.Open   '... ORACLE CONNECTION STRING

    ' READ .SQL INTO A STRING VARIABLE
    With CreateObject("Scripting.FileSystemObject")
          strSQL = .OpenTextFile("C:\Path\To\Script.sql", 1).readall
    End With

    ' RUN COMMAND
    Set cmd = New ADODB.Command
    With cmd
      .ActiveConnection = cnn_Pegase
      .CommandText = sql
      .CommandType = adCmdText

      ' BIND PARAM AND DEFINE TYPE
      .Parameters.Append .CreateParameter("mydate", adDate, adParamInput, , Ma_date)
    End With

    ' OPEN RECORDSET USING COMMAND OBJECT AS SOURCE PARAMETER (NO CONNECTION)
    Set RECSET2 = New ADODB.Recordset 
    RECSET2.Open cmd, , adOpenDynamic, adLockBatchOptimistic

    '... USE RECORDSET

    RECSET2.Close: cnn_Pegase.Close
    Set cmd = Nothing: Set RECSET2 = Nothing: Set cnn_Pegase = Nothing
End Sub