Search code examples
sqlsql-serverstringwhere-clausesql-in

why is the OR being ignored in the WHERE clause?

I have the following Handshake table:

CREATE TABLE [dbo].[Handshake](
    [Report Year] [varchar](100) NULL,
    [Status] [varchar](100) NULL,
    [Update Time] [datetime] NULL,
    [Process Time] [datetime] NULL,
    [Rejects?] [varchar](10) NULL
) ON [PRIMARY]
GO

If i run this query,

SELECT TOP (1) * 
FROM [dbo].[Handshake]
WHERE [Status] <> 'Loading' OR [Status] <> 'Processing' OR [Status] <> 'Processed' OR [Status] <> 'Process Failed'
ORDER BY [Update Time] DESC

i expect anything indicated in the WHERE clause NOT to be returned, but it is!! For example, Processed record should NOT have been returned!!

This is what i get:

record

Sample Data

Report Year Status      Update Time             Process Time            Rejects?
2020 8+4    Processed   2020-10-09 16:58:05.610 2020-10-09 17:20:05.000 NULL
2020 8+4    Processed   2020-10-09 16:22:06.343 2020-10-09 16:53:26.000 NULL
2020 5+7    Processed   2020-09-29 18:09:00.000 2020-10-09 16:04:04.000 TRUE
2020 6+6    Failed  2020-09-29 17:21:00.000 NULL    NULL
2020 5+7    Processed   2020-09-29 15:54:00.000 2020-10-09 16:04:04.000 NULL
Solution

  • You seem to want AND, not OR:

    WHERE [Status] <> 'Loading' 
  AND [Status] <> 'Processing' 
  AND [Status] <> 'Processed' 
  AND [Status] <> 'Process Failed'

    You could also express this with not in:

    WHERE [Status] NOT IN ('Loading', 'Processing', 'Processed', 'Process Failed')