Problem solve
Get help with specific problems with your technologies, process and projects.
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;
