Q

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 sub...

select?

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;

This was last published in January 2003

Dig Deeper on IBM DB2 management

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSOA

SearchSQLServer

Close