Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Not Like including nulls

Status
Not open for further replies.

Ringers

Technical User
Feb 26, 2004
180
AU
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]
 
Because thats the way nulls behave for comparison.

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

Code:
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]

 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top