topic Re: WHERE NOT with multiple criteria in App Development

WHERE NOT with multiple criteria

heitorcarlosgomes — Tue, 05 Dec 2023 12:57:56 GMT

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?

Re: WHERE NOT with multiple criteria

Vegar — Tue, 05 Dec 2023 13:41:05 GMT

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')

);

Re: WHERE NOT with multiple criteria

heitorcarlosgomes — Tue, 05 Dec 2023 14:46:35 GMT

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

Re: WHERE NOT with multiple criteria

heitorcarlosgomes — Tue, 05 Dec 2023 14:48:05 GMT

Your solution is not working, unfortunately...

Re: WHERE NOT with multiple criteria

Vegar — Tue, 05 Dec 2023 22:35:25 GMT

@heitorcarlosgomes

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

Re: WHERE NOT with multiple criteria

PrashantSangle — Wed, 06 Dec 2023 01:58:57 GMT

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

Re: WHERE NOT with multiple criteria

Aasir — Wed, 06 Dec 2023 03:22:23 GMT

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;