WHERE NOT with multiple criteria

[heitorcarlosgomes]

Hey everyone

I'm trouble with Where Not

WHERE NOT

(

(FABRICANTE = 'FACCHINI' or FABRICANTE = 'RANDON')

or (FAMÍLIA = 'DOLLY' or FAMÍLIA = 'CARGA SECA' or FAMÍLIA = 'CANAVIEIRO')

or ([REBOQUE/SEMIRREBOQUE] = 'REBOQUE')

);

I don't know why my output contains all values that I don't want

How can I fix?

[Vegar]

what are you trying to do? 

Using WHER NOT in combination with nested OR statements can be troublesome. Remember that  as  long one of your FABRICANTE, FAMILIA or REBOQUE criterias is not true for a transaction the data will be included in the output.

Could it be that you want to do this?

WHERE NOT

(

(FABRICANTE = 'FACCHINI' or FABRICANTE = 'RANDON')

AND (FAMÍLIA = 'DOLLY' or FAMÍLIA = 'CARGA SECA' or FAMÍLIA = 'CANAVIEIRO')

AND ([REBOQUE/SEMIRREBOQUE] = 'REBOQUE')

);

[heitorcarlosgomes]

Actually, I've written 'and' instead 'or' and I'm still having the same problem.

[heitorcarlosgomes]

Your solution is not working, unfortunately...

[Vegar]

@heitorcarlosgomes 

Can you try to describe in word how your where statement should filter the data you want?

[PrashantSangle]

try with not wildmatch()

try below

not wildmatch(FABRICANTE,'FACCHINI','RANDON')

and not wildmatch(FAMÍLIA,'DOLLY','CARGA SECA','CANAVIEIRO')

and not wildmatch([REBOQUE/SEMIRREBOQUE],'REBOQUE')

since we are not familiar with your data & output which you are looking for. So, Choose and / or as per your need. also, you can use bracket to club to condition in one group.

Regards,

Prashant Sangle

[Aasir]

Smaller change done to your query

• UPPER to make the comparisons case-insensitive.
• added checks for NULL values in the columns.

SELECT *
FROM YourTable
WHERE NOT (
(UPPER(FABRICANTE) = 'FACCHINI' OR UPPER(FABRICANTE) = 'RANDON')
OR (UPPER(FAMÍLIA) = 'DOLLY' OR UPPER(FAMÍLIA) = 'CARGA SECA' OR UPPER(FAMÍLIA) = 'CANAVIEIRO')
OR (UPPER([REBOQUE/SEMIRREBOQUE]) = 'REBOQUE')
)
AND FABRICANTE IS NOT NULL
AND FAMÍLIA IS NOT NULL
AND [REBOQUE/SEMIRREBOQUE] IS NOT NULL;