Changing an empty value to NULL???

Question

Post reply

Changing an empty value to NULL???

ninel

SSC Eights!

Points: 977
More actions
January 2, 2007 at 3:52 pm

#171660

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

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

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

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

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

Per Bylund-388363 Old Hand Points: 368 More actions · Answer 2

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.

ninel SSC Eights! Points: 977 More actions · Answer 3

ninel

SSC Eights!

Points: 977

January 3, 2007 at 7:34 am

#680959

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?