CODEWITHSUNDEEP
oracle-databaseselectcase
user2121274
user2121274

Reputation: 13

ORA-00936: missing expression case statement

Received the above error when executing this select with a case statement. I tried several methods, but cannot get the syntax correct.

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

Upvotes: 1

Views: 6118

Answers (1)

cha
cha

Reputation: 10411

you need to rewrite your query like this:

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

Basically, you can't return multiple columns from a CASE statement. Treat it as a conditional replacement for a single column. In the example above, I used the same CASE conditions for both BORROWER_ORG_ID and LENDER_ORG_ID aliases.

On the side note, you should also consider including an ELSE statement into your CASEs. Otherwise, if THV.BORROW_LOAN_TYPE_CD is not B or L you would get NULLs

Upvotes: 1

Related Questions