ORA-00936:缺少表达式case语句

Question

在使用case语句执行select时收到上述错误。我尝试了几种方法，但无法获得语法正确。

SELECT m.md_security_id,
   thv.unit_qty,
   thv.contract_value_amt,
   thv.contract_currency_cd,
   (case when
   THV.BORROW_LOAN_TYPE_CD = 'B'
   then select THV.SENDER_ORG_ID as BORROWER_ORG_ID,
           select THV.RECIPIENT_ORG_ID as LENDER_ORG_ID
     end),
   (case when
   THV.BORROW_LOAN_TYPE_CD = 'L'
   THEN select THV.RECIPIENT_ORG_ID as BORROWER_ORG_ID,
            select THV.SENDER_ORG_ID as LENDER_ORG_ID
    end)   
    FROM eqmddev.txn_header_mv thv,
   eqmddev.md_security m,
   eqmddev.security_xref_vw_tbl x
   WHERE     m.isin = x.security_id
   AND ThV.SECURITY_MASTER_ID = X.SECURITY_MASTER_ID
   AND M.EXCHANGE_CD = X.EXCHANGE_CD

Answer 1

你需要像这样重写你的查询:

SELECT m.md_security_id,
   thv.unit_qty,
   thv.contract_value_amt,
   thv.contract_currency_cd,
   case 
    when THV.BORROW_LOAN_TYPE_CD = 'B' then THV.SENDER_ORG_ID 
    when THV.BORROW_LOAN_TYPE_CD = 'L' THEN THV.RECIPIENT_ORG_ID 
   end AS BORROWER_ORG_ID,
   case 
    when THV.BORROW_LOAN_TYPE_CD = 'B' then THV.RECIPIENT_ORG_ID 
    when THV.BORROW_LOAN_TYPE_CD = 'L' THEN THV.SENDER_ORG_ID 
   end AS LENDER_ORG_ID,
FROM eqmddev.txn_header_mv thv,
   eqmddev.md_security m,
   eqmddev.security_xref_vw_tbl x
WHERE     m.isin = x.security_id
   AND ThV.SECURITY_MASTER_ID = X.SECURITY_MASTER_ID
   AND M.EXCHANGE_CD = X.EXCHANGE_CD

基本上，不能从CASE语句返回多个列。将其视为单个列的条件替换。在上面的示例中，我对BORROWER_ORG_ID和LENDER_ORG_ID别名使用了相同的CASE条件。

另一方面，您还应该考虑在case中包含ELSE语句。否则，如果THV.BORROW_LOAN_TYPE_CD不是B或L，您将获得null