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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top