CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Sohail xIN3N
Sohail xIN3N

Reputation: 3041

ORA-01427 "Single-row subquery returns more than one row" with TWO CASES [Oracle DB]

I'm having an issue with the following query and unable to figured out where I'm wrong.

SELECT * FROM TBLCUSTCHANNELACCT b WHERE
 (CASE WHEN NVL('0001, 0015', '-1') = '-1' THEN NVL('0001, 0015', '-1')
      ELSE b.productid END
         IN
  CASE WHEN NVL('0001, 0015', '-1') = '-1' THEN NVL('0001, 0015', '-1') 
      ELSE
   (SELECT a.product_id FROM tblcfgproductdetail a WHERE a.productcode IN ('0001', '0015') )
END);

Any help would be appreciated Thanks.

Upvotes: 0

Views: 907

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Don't use case. Just use basic logic:

SELECT b.*
FROM TBLCUSTCHANNELACCT b
WHERE ('0001, 0015' IS NULL) OR
      b.productid IN (SELECT a.product_id
                      FROM tblcfgproductdetail a
                      WHERE a.productcode IN ('0001', '0015')
                     );

I'm pretty sure this is the intention of your query.

Upvotes: 2

Related Questions