1. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Oct 2012
Posts
3
Rep Power
0

#### 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
2. No Profile Picture
Contributing User
Devshed Intermediate (1500 - 1999 posts)

Join Date
Mar 2008
Posts
1,949
Rep Power
380
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?
3. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Oct 2012
Posts
3
Rep Power
0

#### 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,...
4. No Profile Picture
Contributing User
Devshed Intermediate (1500 - 1999 posts)

Join Date
Mar 2008
Posts
1,949
Rep Power
380
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.
5. 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