Changing an empty value to NULL???

Question

Changing an empty value to NULL???

ninel

Ten Centuries

Points: 1087
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 SSC-Addicted Points: 428 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 Ten Centuries Points: 1087 More actions · Answer 3

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?

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 4

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

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 5

Dont you have a PK on the tables?

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

noeld SSC Guru Points: 96590 More actions · Answer 6

select nullif( columnname,'')

from your table

* Noel