Ask the Expert

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

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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: