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