5/1/2024 5:29:58 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
|
|
|
|
NOT
Posted: 21 Aug 06 9:56 AM
|
fiogf49gjkf0d I am trying to create a group where the email address does not contain (see below) and the opt out does not contain (see below). However, the second Not doesn't work. Any suggestions? SELECT DISTINCT A1.CONTACTID, A1.EMAIL, A1.ACCOUNT, A2.NO_EMAIL A2_NO_EMAIL FROM CONTACT A1 LEFT OUTER JOIN CSC_CONTACT A2 ON (A1.CONTACTID=A2.CONTACTID) WHERE (A1.EMAIL IS NOT NULL) AND (A2.NO_EMAIL IS NULL) OR NOT ((UPPER(A2.NO_EMAIL) LIKE '%UCC%') OR (UPPER(A2.NO_EMAIL) LIKE '%ALL MARKETING%')) OR NOT ((UPPER(A1.EMAIL) LIKE '%@CSCINFO%') OR (UPPER(A1.EMAIL) LIKE '%@ENTITYSERVICES%')) ORDER BY A1.EMAIL ASC
|
|
|
|
Re: NOT
Posted: 21 Aug 06 3:45 PM
|
fiogf49gjkf0d Do you have access to something like Query Analyzer (Execute SQL command in Administrator works just as well) where you can perfect the SQL part of the query then rebuild it in the group? Usually the issue has to do with not setting parenthesis in the right place, or the use of AND when OR should be used (or vice versa). I've also encountered groups where condition order matters but that is extremely rare. |
|
|
|
Re: NOT
Posted: 22 Aug 06 11:07 AM
|
fiogf49gjkf0d Hmm...If you are using SQL Server do you know if the DB case sensitive? By default it is not. That alone will eliminate your need to use the UPPER() function. Therefore reducing the complexity of the WHERE clause. Leaving the UPPER function in place its possible that the problem might be with parenthesis. More are needed. I usually try to surround related AND comparisons with parenthesis and the ORs are the connectORs. I think where I have the bolded parens is what are needed.
WHERE
((A1.EMAIL IS NOT NULL) AND (A2.NO_EMAIL IS NULL)) OR NOT ((UPPER(A2.NO_EMAIL) LIKE '%UCC%') OR (UPPER(A2.NO_EMAIL) LIKE '%ALL MARKETING%')) OR NOT ((UPPER(A1.EMAIL) LIKE '%@CSCINFO%') OR (UPPER(A1.EMAIL) LIKE '%@ENTITYSERVICES%'))
|
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|