and / or

Hi,
Can you see if this query is correct?
I would like to make sure the select query returns fieldID only if any of the fields inside the bracket (field4, 5, 6, 7) has changed.

--has anything for this record changed?...
--if so, add it into the Differences table...
select
fieldID
from
tblTable
where
fieldID = @.fieldID and
fieldID2 = @.NB
and
( field3 != @.field3 or field4 != @.field4
or field5 != @.field5
or field6 != @.field6
or field7 != @.field7
)This will work if all of your fields do not allow null; however, you will need to test for nulls if your columns allow for nulls.|||

Some of the fields do allow for nulls.

You mean I should include nullif ?

|||

You will need to change

Code Snippet

field3 != @.field3

into

Code Snippet

field3 != @.field3 or field3 is null and @.field3 is not null or field3 is not null and @.field3 is null

You will need to do this or something similar using ISNULL or COALESCE for all of your fields that you are testing for not equal.

|||

You could use the CHECKSUM() function to compare the left and right sides of the WHERE clause.

As this example demonstrates, data conversions are not required and null values will also work.

Code Snippet

DECLARE
@.Param1 varchar(20),
@.Param2 varchar(20),
@.Param3 int,
@.Param4 int,
@.NullValue varchar(10)

SELECT
@.Param1 = 'Test',
@.Param2 = 'Test2',
@.Param3 = 25

IF checksum( @.Param1, @.Param2, @.Param3, @.Param4 ) = checksum( 'Test', 'Test2', 25, @.NullValue )
PRINT 'Match'
ELSE
PRINT 'NoMatch'

So for your needs, you would use:

AND checksum( Field3, Field4, Field5) <> checksum( @.Field3, @.Field4, @.Field5)

(List shortened for display purposes...)

|||

Arnie Rowland wrote:

You could use the CHECKSUM() function to compare the left and right sides of the WHERE clause.

Please don't use CHECKSUM or BINARY_CHECKSUM functions. They are not guaranteed to produce unique values for input. They are simple hash functions used to divide set of values into different ranges (for example to create compact indexes or partition the data). In fact, with the current implementation you can get duplicate checksum values quite easily and there are certain types of input values that will simply produce unexpected results (repeated values, NULLs etc). You could use hashbytes in SQL Server 2005 which can generate MD5 or MD4 hash for example which can avoid collisions but still no guarantee to produce unique value for each input.

|||

You can change your WHERE clause to below since indexes if any on the columns (those with the OR checks) will not really help.

and
( coalesce(field3, '') <> coalesce(@.field3, '') -- use appriate data type value '' or 0 and so on.

-- repeat for others

or field4 != @.field4
or field5 != @.field5
or field6 != @.field6
or field7 != @.field7
)