CODEWITHSUNDEEP
sqlsql-serverwhere-clause
GLOBALJJ
GLOBALJJ

Reputation: 25

Select query WHERE clause - Exclude specific records

I have a simple usp (basically a Select query). In the WHERE clause I need to exclude a specific value ([Instrument_Classification] of 'MXXOIS') that have a [Price_Currency] in ('CNY', 'BRL', 'CLP', 'THB'). All other [Instrument_Classification] of 'MXXOIS' that have [Price_Currency] of currencies other than the 4 noted above must be included. There are however hundreds of records with a [Instrument_Classification] of NULL, which must be included. The query looks as follows:

SELECT [Instrument_Classification], [Report_Status], [Price_Currency],
        [Transaction_Reference_Number], [Venue_Transaction_ID], [Executing_Entity_ID],
        [Date Received]
FROM [Transactions].[dbo].[TradeMessage]
WHERE  [Date Received] between '2022-10-27 00:00:00' and '2022-10-27 23:59:59'
AND ([Instrument_Classification] <> 'MXXOIS' 
        and [Price_Currency] in ('CNY', 'BRL', 'CLP', 'THB') 
        or [Instrument_Classification] is NULL
    )

The query returns all [Instrument_Classification] records with NULL values correctly but excludes all records with [Instrument_Classification} of MXXOIS plus also excludes all other records with [Instrument_Classification} other than 'MXXOIS e.g. code FFICSX'

The query should only exclude [Instrument_Classification} of MXXOIS with a [Price_Currency] in ('CNY', 'BRL', 'CLP', 'THB').

Any MXXOIS record with a [Price_Currency] other than the 4 mentioned should be included.

My testing also, at some point, caused [Instrument_Classification] records with NULL with a [Price_Currency] in ('CNY', 'BRL', 'CLP', 'THB') to be excluded.

This is not correct as the only records to be excluded is [Instrument_Classification} of MXXOIS with a [Price_Currency] in ('CNY', 'BRL', 'CLP', 'THB')

Anyone able to help on my WHERE clause please?

Upvotes: 0

Views: 427

Answers (1)

GMB
GMB

Reputation: 222432

This first reads like a precedence issue. OR has lower priority than AND, so you need to be explicit about the expected order of operations when mixing both ; you do this by wrapping the predicates in parentheses.

So this:

[Instrument_Classification] <> 'MXXOIS' 
and [Price_Currency] in ('CNY', 'BRL', 'CLP', 'THB') 
or [Instrument_Classification] is NULL

Should be written :

(
    [Instrument_Classification] <> 'MXXOIS' 
    and [Price_Currency] in ('CNY', 'BRL', 'CLP', 'THB') 
)        
or [Instrument_Classification] is NULL

But since you said...

I need to exclude a specific value ([Instrument_Classification] of 'MXXOIS') that have a [Price_Currency] in ('CNY', 'BRL', 'CLP')

I don't think the above condition does exactly what you want. Typically, your query excludes classifications MXXOIS, regardless of the currency.

I would suggest:

not (
    [Instrument_Classification] = 'MXXOIS' 
    and [Price_Currency] in ('CNY', 'BRL', 'CLP', 'THB') 
)        
or [Instrument_Classification] is NULL

Or we can use ORs:

[Instrument_Classification] <> 'MXXOIS' 
or [Price_Currency] <> ('CNY', 'BRL', 'CLP', 'THB') 
or [Instrument_Classification] is NULL

Upvotes: 1

Related Questions