Logic is not wokring

Question

Post reply

Logic is not wokring

ajoe

SSC Veteran

Points: 269
More actions
March 1, 2021 at 11:36 pm

#3851957
hi,
I have 3 types of invoices , INNU,INN1, INN2
I am trying to filter out invoices that are "INN1" and "Unpaid" ( I dont want INN1 Unpaid invoices )
I tried to use the following condition but Its taking out all the "Unpaid" invoice.
where [Invoice Class] IN (@invoice) and ( invoice_cl <> 'INN1' and [Payment Status] <> 'Unpaid')

But then I changed the condition and used the following and got the correct result.
[Invoice Class] IN (@invoice) and not (invoice_cl in('INN1') and [Payment Status] = 'Unpaid')
Can anyone tell me ultimately what is the difference between these 2 conditions.
First condition is not working as expected but second one is giving me the correct result.
- This topic was modified 3 years, 2 months ago by ajoe.

March 2, 2021 at 3:16 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 1

It's been a while since I've messed with negative boolean algebra so I hope I got this right...

The first one equates to NOT (invoice_cl = 'INN1' OR PaymentStatus = 'Unpaid'). That means that anything with an 'IIN1' will be excluded and anything with an 'Unpaid' in the rows will be excluded whether or not it's an 'IIN1' row or not.

The second one states that both 'INN1' AND 'UnPaid' must be in the same row and, if they are, only then exclude it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18271 More actions · Answer 2

not (invoice_cl in('INN1') and [Payment Status] = 'Unpaid')
= (invoice_cl not in('INN1') or [Payment Status] <> 'Unpaid')
= (invoice_cl <> 'INN1' or [Payment Status] <> 'Unpaid')

so the statement

[Invoice Class] IN (@invoice) and not (invoice_cl in('INN1') and [Payment Status] = 'Unpaid')

is equivalent to

[Invoice Class] IN (@invoice) and (invoice_cl <> 'INN1' or [Payment Status] <> 'Unpaid')

So in your first statement you have an AND where you should have an OR

ajoe SSC Veteran Points: 269 More actions · Answer 3

Thank you Jonathan AC Roberts and Jeff Moden for replying back to me. your explanations were really helpful.:)

This reply was modified 3 years, 2 months ago by ajoe.