none
NULL values issue

    Question

  • Hi

    I need to use this condition

    WHERE  sale_cancel_Reason__c not in('Duplicate')

    I have below values


    NULL

    NULL

    NULL

    NA

    NULL
    NULL
    NA
    NA

    But it return me NO values where it is null.

    When i used

    SET

    QUOTED_IDENTIFIER OFF

    Then it return me values. but same is not working in View

    Please help me

    • Edited by BI_group Monday, July 15, 2013 10:08 AM
    Monday, July 15, 2013 9:58 AM

Answers

  • This is because a value that is unknown cannot be compared logically against any other value.

    Please refer these two links:

    NOT IN vs. NOT EXISTS vs. LEFT JOIN

    ANSI_NULLS


    http://sqldevelop.wordpress.com/

    • Marked as answer by BI_group Wednesday, July 17, 2013 11:48 AM
    Monday, July 15, 2013 11:03 AM
  • Hi BI_group,

    SET QUOTE_IDENTIFIER has nothing do with null handling. For returning the null values you need use "SET ANSI_NULLS OFF" . (To learn more please refer this link - > http://msdn.microsoft.com/en-us/library/ms188048.aspx)

    If you want to return null values this setting has to be set before running the query.

    For example 

    ----Create Table 
    CREATE TABLE NULLCHECK 
    (
      ColA VARCHAR(100)
    )
    
    
    ----Insert Value
    INSERT INTO NULLCHECK VALUES (NULL),('Test')
    
    ----CREATE View
    CREATE VIEW v_NULLCHECK
    AS
    SELECT n.* FROM NULLCHECK n 
    
    ---------SET ANSI_NULLS OFF
    SET ANSI_NULLS OFF
    SELECT * FROM v_NULLCHECK WHERE ColA not IN ('Test')
    -----REsult 
    NULL
    
    ---------SET ANSI_NULLS ON
    SET ANSI_NULLS ON
    SELECT * FROM v_NULLCHECK WHERE ColA not IN ('Test')
    ---REsult (Blank)


    Regards Harsh


    Monday, July 15, 2013 11:19 AM
  • hi

     If your dealing with NULL value use SET ANSI_NULLS OFF,because sql server treat NULL as unknown value.

    • Marked as answer by BI_group Wednesday, July 17, 2013 11:48 AM
    Monday, July 15, 2013 12:22 PM

All replies

  • Hi,

    in SQL NULL Is handled as "Unknown". 

    Null Values

    try this to fix your query

     sale_cancel_Reason__c not in('Duplicate') Or sale_cancel_Reason__c is null




    Satheesh


    Monday, July 15, 2013 10:09 AM
  • This is normal SQL behavior - It has to do with 3 valued logic. Basically when you use NOT IN and the subquery contains NULLS, no results will be returned.

    The easiest method of going around this is you use a correlated subquery.

    where not exists ()

    Monday, July 15, 2013 10:09 AM
  • This is because a value that is unknown cannot be compared logically against any other value.

    Please refer these two links:

    NOT IN vs. NOT EXISTS vs. LEFT JOIN

    ANSI_NULLS


    http://sqldevelop.wordpress.com/

    • Marked as answer by BI_group Wednesday, July 17, 2013 11:48 AM
    Monday, July 15, 2013 11:03 AM
  • Hi BI_group,

    SET QUOTE_IDENTIFIER has nothing do with null handling. For returning the null values you need use "SET ANSI_NULLS OFF" . (To learn more please refer this link - > http://msdn.microsoft.com/en-us/library/ms188048.aspx)

    If you want to return null values this setting has to be set before running the query.

    For example 

    ----Create Table 
    CREATE TABLE NULLCHECK 
    (
      ColA VARCHAR(100)
    )
    
    
    ----Insert Value
    INSERT INTO NULLCHECK VALUES (NULL),('Test')
    
    ----CREATE View
    CREATE VIEW v_NULLCHECK
    AS
    SELECT n.* FROM NULLCHECK n 
    
    ---------SET ANSI_NULLS OFF
    SET ANSI_NULLS OFF
    SELECT * FROM v_NULLCHECK WHERE ColA not IN ('Test')
    -----REsult 
    NULL
    
    ---------SET ANSI_NULLS ON
    SET ANSI_NULLS ON
    SELECT * FROM v_NULLCHECK WHERE ColA not IN ('Test')
    ---REsult (Blank)


    Regards Harsh


    Monday, July 15, 2013 11:19 AM
  • hi

     If your dealing with NULL value use SET ANSI_NULLS OFF,because sql server treat NULL as unknown value.

    • Marked as answer by BI_group Wednesday, July 17, 2013 11:48 AM
    Monday, July 15, 2013 12:22 PM
  • That is an absolutely bad recommendation for many reasons - perhaps foremost is that this setting is already deprecated.  The best approach is to use the correct logic - which has already been given.
    Monday, July 15, 2013 1:18 PM
  • Please do not use SET ANSI_NULL OFF options

    rather i would recommend using 

    Or sale_cancel_Reason__c is null

    Satheesh

    Wednesday, July 17, 2013 12:07 PM