ADODB:if在sql子句中

Question

我是VBA的新手，我想知道如何有条件地构建新列：我想从cl.CD_EVT:列创建VENTE列

cl.CD_EVT(如果cl.CD_EV在('COPVT'，'XCOPVT'，'3COPVT'、'OCVT'，'XCOCVT'、'3COCVT'(中，则VENTE＝"；OU"；，否则VENTE＝"；NON"(">

但我认为这不是VBA的正确语法。

我的代码是：

RECSET2.Open " 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, cl.CD_EVT (case cl.CD_EVT when 'COPVT' then 'OUI' when 'XCOPVT' then 'OUI' when '3 COPVT' then 'OUI' when 'COCVT' then 'OUI' when 'XCOCVT' then 'OUI' when '3 COCVT' then OUI else 'NON') 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 " & _

谢谢你的帮助！

Answer 1

您正在寻找CASE WHEN...THEN...END；将您的if条件移动到WHEN分支，如下所示：

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

确保报价也是一致的'single quotes'；您只需要使用"double quotes"来分隔VBA字符串文字。

---

如果将SQL连接到一个单独的指令中，则它将比发送到连接的位置更容易调试：

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, ...

---

在SQL编辑器中编辑/调试SQL比在VBA字符串文字中进行编辑/调试要容易得多：

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

旁注，这是很多LEFT JOIN；如果其中任何一个可以更改为INNER JOIN，请考虑这样做，它应该会稍微提高查询性能。

所以问题就在这里：

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

应该是：

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

然后我预计GROUP BY子句会抱怨cl.CD_EVT丢失；确保GROUP BY子句包括SELECT子句中的所有非聚合列。

Answer 2

若要了解@MathieuGuidon的正确答案，由于需要使用CASE创建一个新列，请将CASE语句放在SELECT子句下的逗号分隔语句中。您的上述尝试将原始列(cl.CD_EVT(和计算列(VENTE(之间缺少逗号的两列合并在一起，因此引发了Oracle语法错误。

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
, ...               

此外，对于长SQL查询，为了可读性和可维护性，请考虑将所有文本保存在具有所有换行符和空格格式的.sql文件中。要进行参数化，请为要绑定到查询的任何值包含所需的qmark占位符?(不带引号(。然后，将内容读取到VBA中，并使用ADO命令对象，运行参数化查询。这样，就可以完全分离SQL和VBA。

SQL(另存为带有?占位符的.SQL文件(

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(读入.sql并运行参数化查询(

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:PathToScript.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