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;