Is there a way to get around the restriction that the search-condition in CASE expression cannot contain a sub...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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;
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.