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

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    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?
  4. #3
  5. 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,...
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    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.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo