Exclusions not working

[scriptscribe]

Hi,

I'm using the below query to try to exlude any insurance codes that start with a k or v, but it's not working.

AND NOT (FinClassCurr IN('W','C','K','V') OR (LEFT(PrimaryIns,1) IN ('K','V') OR LEFT(SecondaryIns,1) IN ('K','V') Or LEFT(TertiaryIns,1) in ('K','V') OR PrimaryIns = 'M20' Or SecondaryIns = 'M20' OR TertiaryIns = 'M20'))

Any help will be greatly appreciated.

Thanks,
Mike

 

[bborissov]

What not working?
Did you get too much records?
Did you get less records than you expected?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[scriptscribe]

Sorry,

It's still returning rows with primaryins, secondaryins or tertiaryins that start with K or V.

 

[SQLSister]

Do you expect all of those to be affected by the NOT? That won't work that way. Use "Not In" where you have "In" and <> where you have an = and remove the first NOT

Also the ORs might be a problem. You may need AND instead (or parentheses) depending on exactly what results you want. For more specific advice post sample data and sample results you want returned.

"NOTHING is more important in a database than integrity." ESquared

 

[bborissov]

SQLSister,
Why shouldn't?
The whole test is in parentheses. If any of these returns True as a result the whole result will be true and NOT should work.

scriptscribe,
Can you post some data where we could reproduce that behavior?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[scriptscribe]

The data that's still coming up looks like this:
LNM FNM PrimaryINS SecondaryIns TertiaryIns
Smith john K01 X01 M02
Doe Jane X02 V01 A03
Jones Dave X01 M02 V01

 

[bborissov]

Are you sure that all values in these records are LEFT aligned? What if you have a space before the first letter?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[scriptscribe]

I figured out the problem. The current statement is working, it's the previous statement that was used that listed specific insurance codes (K50, V20, etc) that was not working and was reporting inaccurate information.

Thanks all for the help.