Results 1 to 3 of 3

Thread: [RESOLVED] MS SQL - Where IN using a coalesce

  1. #1

    Thread Starter
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Resolved [RESOLVED] MS SQL - Where IN using a coalesce

    Hi

    Nice and simple question:

    Why won't it work ?

    Code:
    ...
    WHERE table.pkid = 1
      AND COALESCE(table.field, '') IN ( '','D' )
    I have data, this particular field is null. Without this coalesce line it brings back the rows. With it it brings back nothing.
    Is the IN clause filtering the data before the COALESCE is applied?

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: MS SQL - Where IN using a coalesce

    I don't know but try isNull(table.field,'').
    Please remember next time...elections matter!

  3. #3

    Thread Starter
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: MS SQL - Where IN using a coalesce

    Nah I got it - I was being silly and added a NOT to the line. Something from the previous statement I was changing. It negated everything, so all ok. Sorry to have bothered you with this

    Thanks for the suggestion

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width