using NOT in a query [ Edited ]

[kristijoy]

Hi Friends!!!!

I'm new to SLX and working on understanding some queries.  I'm having trouble wrapping my head around the NOT field and wonder if perhaps someone could enlightenme...

I thought that the NOT operator would apply to everything within the parenthesis.  It doesn't seem to when I build up my group.  Here's an example (I changed the names of my fields).  I've simplified my query to this to understand it. eventually there will be more things within the first OR group and I was hoping to carve off pieces of it with the NOT's.

(    Contact.StatusDate             is within the last             1100 days                OR

      Contact.ActiveDate              is within the last             1100 days     )        AND

NOT  ( Contact.SatusDate            is within the last         1100 days             AND

           (  Contact.info                        is equal to                    Yes                        OR

               Contact.info                       does not contain data                    ))

I thought the NOT would apply the entire chunk after the parenthesis, but it seems to be using the conditions for contact info on the entire chunk above it (the first OR statement).  Does it have something to do with the date range? 

Am I missing something?

Thanks!

Kristi

[rseiwert]

Did some reformatting to readability.

Your query

(    Contact.StatusDate is within the last 1100 days OR Contact.ActiveDate  is within the last 1100 days     )       

AND 

NOT  ( 

Contact.SatusDate is within the last 1100 days AND

 ( Contact.info  is equal to Yes  OR Contact.info does not contain data)

)

will never return anything. This is basically  Group1 and Not Group2. Anyone with status within 1100 days will be in both. Not 100% where you were trying to go exactly but I think the following will work

(    Contact.StatusDate is within the last 1100 days OR Contact.ActiveDate  is within the last 1100 days     )       

AND

NOT   ( Contact.info  is equal to Yes  OR Contact.info does not contain data)

[rseiwert]

Even better

( Contact.StatusDate is within the last 1100 days OR Contact.ActiveDate  is within the last 1100 days  )       

AND

( Contact.info  is equal to No  OR Contact.info does not contain data)

[kristijoy]

Even better

( Contact.StatusDate is within the last 1100 days OR Contact.ActiveDate  is within the last 1100 days  )       

AND

( Contact.info  is equal to No  OR Contact.info does not contain data)

Thanks for taking the time to answer!

That looks great if you want the part of after the AND ( Contact.info  is equal to No  OR Contact.info does not contain data) to modify the entire first group  Contact.StatusDate is within the last 1100 days OR Contact.ActiveDate  is within the last 1100 days  ) 

I was trying to find a way that the  Contact.info  is equal to No  OR Contact.info does not contain data) would be a condition on only a subset of the group (mainly the ones that were selected in the OR because of the Status Date).

But perhaps you are onto something that modifying the logic to be a 'no'  (effectively to remove the NOT) would allow another AND to identify a subset of the group created by the OR.

Does that make sense?

[rseiwert]

Just need to move some brackets.. This will give you all where statusdate is within 1100 days and info is false or null plus everyone whose activedate is within the last 1100 days. I think this is what you ment.

( 

  Contact.StatusDate is within the last 1100 days       

  AND

  (Contact.info  is equal to No  OR Contact.info does not contain data)

) 

OR

  Contact.ActiveDate  is within the last 1100 days 

[kristijoy]

Thank you so much!  That should help.  One of the things I also figured out too is that NOT in database speak is different than a logic (or boolean) NOT.  I needed to adjust my mind as to what the NOT really meant.  It gave me the rest of the 'set' which was a lot bigger than I had excepted!