Not Like including nulls

[Ringers]

Hi,

I am trying to get records for the previous month and when they aren't finalsed\final. The SQL i have works correctly but the not like is also removing all the records that have a null in the [MTR_STATUS_CD] column.

Can anyone tell me why?

SELECT *
FROM [MTR_MAT_SUPER_TYPE]
WHERE DATEPART(m, [MTR_LODG_DATE]) = DATEPART(m, DATEADD(m, -1, getdate()))
AND [MTR_REG_CODE] is not null
AND [MTR_STATUS_CD] NOT LIKE 'FINAL'
ORDER BY [MTR_JS_CODE]

 

[Alan0568]

Because thats the way nulls behave for comparison.

I allways just use isnull to present an arbitrary value, in this case '' (blank)

SELECT * FROM [MTR_MAT_SUPER_TYPE] WHERE DATEPART(m, [MTR_LODG_DATE]) = DATEPART(m, DATEADD(m, -1, getdate()))
AND [MTR_REG_CODE] is not null

AND isnull([MTR_STATUS_CD],'') NOT LIKE 'FINAL'

ORDER BY [MTR_JS_CODE]

 

[bborissov]

Because NULL is neither equal neither different than any other values (even with NULL).

Consider NULL as SQL Server way to tell you "I don't know".


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.