在Oracle数据库的WHERE子句中使用CASE时出错

Question

这是我的查询，当我运行它时，我会收到错误"缺少关键字"；。有人能帮忙吗

SELECT COUNT(1)  
FROM ar_invoice_master,proposal,proposal_to_opportunity 
WHERE proposal.PROPOSAL_ID=proposal_to_opportunity.FK_PROPOSAL_ID 
AND proposal_to_opportunity.FK_AR_INVOICE_ID=ar_invoice_master.AR_INVOICE_ID 
AND  CASE 
WHEN ar_invoice_master.FK_INVOICE_TYPE_CODE NOT IN ('REN','TRS') THEN ar_invoice_master.AR_INVOICE_OS_AMOUNT>0  
WHEN ar_invoice_master.FK_INVOICE_TYPE_CODE  IN ('REN','TRS') THEN (AR_INVOICE_OS_AMOUNT/AR_INVOICE_TOTAL_AMOUNT * 100)<100
END
AND proposal.FK_GLUSR_USR_ID=: glid

Answer 1

您可以使用AND..OR而不是CASE EXPRESSION:

AND ((ar_invoice_master.FK_INVOICE_TYPE_CODE NOT IN ('REN','TRS')
AND ar_invoice_master.AR_INVOICE_OS_AMOUNT>0)
OR (ar_invoice_master.FK_INVOICE_TYPE_CODE  IN ('REN','TRS')
AND (AR_INVOICE_OS_AMOUNT/AR_INVOICE_TOTAL_AMOUNT * 100)<100))

它不起作用的原因是CASE不是statement而是expression，它不能返回条件。