Q

Can a search-condition in CASE expression contain a subselect?

Find out if a search-condition in CASE expression contain a subselect and learn a workaround to make this happen.

Is there a way to get around the restriction that the search-condition in CASE expression cannot contain a subselect?

Here are two queries I have tried and failed:

1) select userid,
     case
       when exists(select * from bank
           where bankid = user.custid)
         then 'bank'
       else 'non-bank'
     end case
   from user
2) select userid,
     case
       when custid in (select bankid
              from bank)
         then 'bank'
       else 'non-bank'
     end case
   from user

As you have found out, The search condition in a CASE expression cannot contain a subselect. Of course, it is possible to write a query that performs what I believe the intent of your "queries" above to be. One such query follows:

SELECT DISTINCT userid, 'bank'
FROM   user, bank
WHERE  user.custid = bank.bankid
UNION ALL
SELECT DISTINCT userid, 'non-bank'
FROM   USER, BANK
WHERE  NOT EXISTS (SELECT 1 FROM bank WHERE user.custid = bank.bankid);
This query performs an outer join. I did not use SQL outer join syntax but instead used the UNION formulation to get your bank/non-bank designation in the result set. You could instead issue the following query and in the program where an actual value is returned for bankid then substitute 'bank' and where a NULL is returned substitute 'non-bank'.

SELECT user.userid, bank.bankid
FROM   user LEFT OUTER JOIN bank
ON     user.custid = bank.bankid;

This was first published in January 2003
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSOA

SearchSQLServer

Close