Post reply

how to handle a bit field in where clause?

  • August 3, 2010 at 4:35 am

    #227374

    Hi All,

    I have a bit field column (confirmed) in a table, which can take true,false or null. I want to use this field in where clause of a query and need to compare with similar field from some other table. Result set will be different for all the 3 possible values of confirmed column.

    Query is like this.

    Select * from tbl1 Where confirmed in (select confirmed from tbl2)

    I am getting expected results when confirmed field in tbl2 is not null.

    Can someone help me to resolve this 'null' issue?

    Thanks in advance

    Raghavendra NS

  • August 3, 2010 at 5:07 am

    This was removed by the editor as SPAM

  • August 4, 2010 at 3:44 am

    #1201944

    Also it can be written as

    Select * from tbl1 Where isnull(confirmed, -1) in (select distinct isnull(confirmed, -1) from tbl2)

  • August 4, 2010 at 7:15 am

    #1202073

    Just remember that using a function on a field in the left-hand side of an expression will mean that you can't utilize an index to do a seek....

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply