November 1st, 2012, 04:33 AM

Case when exists
I have the following code:
CASE WHEN EXISTS (SELECT 1 FROM DAAN.MICTOT1208 WHERE ID08 = 160100) THEN 1 ELSE 0 END AS test001
This creates the column 'test001' with all values equal to 1, because 160100 is in column 'ID08'.
What I want is (instead of the 160100 above) a case by case check whether the value of column ID09 is in column ID08. So something like:
CASE WHEN EXISTS (SELECT 1 FROM DAAN.MICTOT1208 WHERE klantnummer = {valueID09}) THEN 1 ELSE 0 END AS test001
But obviously this does not work
Last edited by DaanOoms; November 1st, 2012 at 04:39 AM.
Reason: typo
November 1st, 2012, 04:46 AM

I don't follow. Can you provide a small but representative example  ideally in way which allows us to quickly and easily replicate your problem?
November 1st, 2012, 04:58 AM

case when exists
Dear Latte,
Thank you for the quick reply.
It is actually the same question as yesterday. So the data is:
A B output
1 1 0
2 2 0
2 . 0 (because 2 is somewhere in B)
3 . 1 (because 3 is nowhere in B)
The solution you sent yesterday perfectly works. However, I want to do it within a 'CREATE TABLE' routine.
CASE WHEN EXISTS (SELECT 1 FROM Table1 WHERE A = 2) THEN 0 ELSE 1 END AS output
gives:
A B output
1 1 0
2 2 0
2 . 0
3 . 0
Because 2 is somewhere in B.
However what I want is:
A B output
1 1 0
2 2 0
2 . 0 (because 2 is somewhere in B)
3 . 1 (because 3 is nowhere in B)
I am new to SQL, after many years in SPSS and STATA. I though it should be possible to have something like
CASE WHEN EXISTS (SELECT 1 FROM Table1 WHERE A = {valueA}) THEN 0 ELSE 1 END AS output
where {valueA} is the case by case changing value of A. So in the first line it checks whether 1 exists in B, in the second line it checks whether 2 exists in B,...
November 1st, 2012, 08:10 AM

No. I still don't understand what you're trying to do, what part 'CREATE TABLE' has to play in it, what aspect of the solution already provided is inadequate, or indeed what problem it is that you are actually trying to solve.
Step away from the keyboard, have a cup of coffee, and then try again. To maintain readability, enclose code in [ code ][ /code ] tags (minus the spaces) and provide DDLs for all relavant tables and sample data so that we may more easily replicate your problem.
November 1st, 2012, 10:12 AM

i've said it before and i'll say it again...
when you use fake table and column names, you're hiding your real problem from the very people who are trying to help you
if you want us to understand why "because 3 is nowhere in B" is important to you, let us in on what A and B mean