Need to verify and "AND" tsql

1

Hi.

select * from X
where ....etc

and not (TransI_strReceiptNumber ='' or TransI_strReceiptNumber is null)
and not (transi_strtype = 'S' and TransI_strStatus = 'R')

What I need to do is not remove every transaction that is not empty or null on TransI_strReceiptNumber but also the transaction should not be of transi_strtype = 'S' and TransI_strStatus = 'R'

So if a transaction is like so
Trid transi_strtype TransI_strStatus TransI_strReceiptNumber
1000 S R NULL

It needs not to appear in the select statement.

Is the above correct? I think it is but I'm not 100% sure on the parenthesis. Should I just put a big one (parenthesis) and include the whole statement?
p.e. -- and not ( (TransI_strReceiptNumber ='' or TransI_strReceiptNumber is null) and (transi_strtype = 'S' and TransI_strStatus = 'R') )

2

If you can post a truth table such as shown below (this is only a sample, not complete and probably not correct), that would make it easier for someone to write the logic

TransI_strReceiptNumber transi_strtype 	TransI_strStatus Exclude?
Empty					Any				Any				Exclude
NULL					Any				Any				Exclude
Non-empty				S				R				Include
Non-empty				S				Any				Include

Based on your description, which I admit I did not completely follow, perhaps this?

AND 
(
	(TransI_strReceiptNumber <> '' AND TransI_strReceiptNumber is NOT NULL NULL)
	OR
	(transi_strtype = 'S' and TransI_strStatus = 'R')
)
3

Presumably don't need the second condition there as

TransI_strReceiptNumber <> ''

will, also, exclude NULLs

EDIT: barring some Goofy ANSI setting option I suppose ...

4

Hi.
Yes I don't know about ansi but if i do not use the <>'' it will bypass rows that are '' and not null.

So is my query correct?

James, i think you query has some issues. (is NOT NULL NULL) also why "OR" ? I need all conditions to match, not one or the other.

thanks

5

Yes, but your query is a NOT - that has a different effect:

not (TransI_strReceiptNumber = '')

matches NULL whereas

TransI_strReceiptNumber <> ''

matches anything which is neither blank string nor NULL