Changing an empty value to NULL???

  • I have two tables: A and B. A is the main table and B is the exception table. What I need to do is check if anything in Table A exists in table B based on a couple of fields.

    Ex:

                                                                                                                                                                           SELECT [sCustomerFullName], [sAccountNumber], [sSIM], [sPlanCode]FROM A a                                                                                                                                                                    WHERE EXISTS  (SELECT * FROM B b  WHERE a.ssim = b.sSim AND a.sTMobilePlanCode = b.sTMobilePlanCode)

    Let's say Table A contains custname(Joe Shmoe), but the sSIM is empty (because the table doesn't allow NULL values). This customer DOES NOT exist in table B. This record shouldn't be returned because it doesn't exist in table B, but it is  being returned because the sSIM is empty.

    I played with my own copy of the table A and set up sSIM as allowing NULL values. Using the query above did not return the record.

    Does anyone know how to get around this if the field does not allow NULLs?

    Thanks,

    Ninel

     

     

     

  • Can you check for something that will always have some data, maybe sAccountNumber.  I think that not allowing NULL isn't your problem. 

    For example, if I have 30 people with the same sTMobilePlanCode and 15 of them have "empty" ssim, they are all going to match on an empty ssim in B.  The reason that changing to NULL didn't return the person you were looking for is that comparing anything to NULL will always return false.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • I'm not sure I understand your problem correctly, but wouldn't checking for null do the trick? I mean like this:

     

    SELECT [sCustomerFullName], [sAccountNumber], [sSIM], [sPlanCode]FROM A a  WHERE EXISTS (

    SELECT * FROM B b  WHERE IsNull(a.ssim, '') = IsNull(b.sSim, '') AND IsNull(a.sTMobilePlanCode, '') = IsNull(b.sTMobilePlanCode, '')

    )

    It might be marginally slower, but it would work.

  • The problem is that the sSIM value is not null, it is an empty string. Is there any string function that can convert an empty string to a NULL so that I can compare?

  • Comparing to NULL is problematic even when you compare NULL to NULL.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • Dont you have a PK on the tables?

    ******************
    Dinakar Nethi
    Life is short. Enjoy it.
    ******************

  • select nullif( columnname,'')

    from your table


    * Noel

Viewing 7 posts - 1 through 6 (of 6 total)

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