Comparing possible NULLS

[naxy]

Hi all,

I'm trying to write a data validating query in which existing data is checked against new data coming come. I'm then determining if I need to insert a new record or simply update one already existing. The insert records are good to go, but I can't get the update comparison to go. Here's a brief look at what I've got:

UPDATE a
SET a.PUSH_STATUS = 'U'
select *
FROM HR_SRC a
INNER JOIN EMPLE_jlr b
ON a.Employee_ID = b.HMN_RSRC_ID
WHERE
a.Previous_Badge_ID <> b.TEMP_ID
OR a.LAN_ID <> b.LAN_ID
OR a.First_Name <> b.FST_NM
OR a.Middle_Initial <> b.MID_INIT
OR a.Last_Name <> b.LST_NM

There's more to the end of the query (as I have to check a total of about 20 fields for change), but that gives you a good idea. There's no problem when there is data in both fields. The problem lies in that a NULL comparison isn't being done; for example, if the TEMP_ID is NULL and the Previous_Badge_ID is 'TEST', the comparison of 'a.Previous_Badge_ID <> b.TEMP_ID' is not being done correctly because of the NULL. Well, I suppose the comparison is being done correctly as per the SQL NULL standards, but I need to be able to do this kind of check.

Anyone have any suggestions?

 

[naxy]

Oops. Disregard the lower case "select *" line. It's there for my testing in Query Analyzer.

 

[naxy]

Apologies for the triple post here, but I think I may have stumbled upon my solution. Does this look right?

UPDATE a
SET a.PUSH_STATUS = 'U'
FROM HR_SRC a
INNER JOIN EMPLE_jlr b
ON a.Employee_ID = b.HMN_RSRC_ID
WHERE
IsNull(a.Previous_Badge_ID, '') <> IsNull(b.TEMP_ID, '')

 

[AlexCuse]

I would use COALESCE(blah, '') rather than isnull.

And I assume that you are going to do the same for your other comparison columns?

If so, then yeah I think you will be good.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[naxy]

I'll give COALESCE a shot. I'm just so used to using IsNull on the few database fields that we allow them on. Oh, and yeah, same principle applies for all fields I need to check against.

Thanks!