Ignoring Does Not Contain

Ask a question

Saturday, February 09, 2013 8:20 PM

0
Can anyone tell me why this code is ignoring the <>?

It is including the records that contain "No report required" and "Report received"

SELECT     tblCMEPers.PersNameLast, tblCMEPers.PersNum, tblCurrentReportStatus.CaseNum, tblCurrentReportStatus.PRODStatus,
                      tblFDMIVouchers.PaidStatus, tblCase.FieldInvestigator, tblFDMIVouchers.Mileage, tblCase.CaseType, tblAutopsy.ExamType, tblCase.FDMI,
                      tblCase.NameLast, tblCase.NameFirst, tblCase.NameMiddle, tblCMEPers.PersAddr, tblFDMIVouchers.PaidStatusDate,
                      tblCurrentReportStatus.PRODStatusDate, tblCase.PrnByDate, tblCurrentReportStatus.FieldExternalStatus, tblCMEPers.PersCity, tblCMEPers.PersState,
                      tblCMEPers.PersZip, tblCMEPers.PersType
FROM         tblCurrentReportStatus INNER JOIN
                      tblFDMIVouchers ON tblCurrentReportStatus.CaseNum = tblFDMIVouchers.CaseNum INNER JOIN
                      tblCase ON tblCurrentReportStatus.CaseNum = tblCase.CaseNum INNER JOIN
                      tblAutopsy ON tblCurrentReportStatus.CaseNum = tblAutopsy.CaseNum INNER JOIN
                      tblCMEPers ON tblCase.FDMI = tblCMEPers.PersNum
WHERE     (tblCurrentReportStatus.PRODStatus = 'No report received') AND (tblCMEPers.PersType = 'FDMI') AND
                      (tblCurrentReportStatus.PRODStatus <> 'Report received') OR
                      (tblCurrentReportStatus.PRODStatus <> 'No report required') OR
                      (tblFDMIVouchers.PaidStatus = 0) OR
                      (tblCurrentReportStatus.FieldExternalStatus = 'No report received') OR
                      (tblCurrentReportStatus.FieldExternalStatus <> 'Report received') OR
                      (tblCurrentReportStatus.FieldExternalStatus <> 'No Report required')
ORDER BY tblCMEPers.PersNameLast

Gee
- Moved by Fanny LiuMicrosoft Contingent Staff Tuesday, February 12, 2013 10:02 AM better support
- Reply
- Quote

All Replies

Sunday, February 10, 2013 1:35 AM

0

The one above returns ALL the data, including the ones I want excluded and this one returns nothing:

SELECT     tblCMEPers.PersNameLast, tblCMEPers.PersNum, tblCurrentReportStatus.CaseNum, tblCurrentReportStatus.PRODStatus,
                      tblFDMIVouchers.PaidStatus, tblCase.FieldInvestigator, tblFDMIVouchers.Mileage, tblCase.CaseType, tblAutopsy.ExamType, tblCase.FDMI,
                      tblCase.NameLast, tblCase.NameFirst, tblCase.NameMiddle, tblCMEPers.PersAddr, tblFDMIVouchers.PaidStatusDate,
                      tblCurrentReportStatus.PRODStatusDate, tblCase.PrnByDate, tblCurrentReportStatus.FieldExternalStatus, tblCMEPers.PersCity, tblCMEPers.PersState,
                      tblCMEPers.PersZip, tblCMEPers.PersType
FROM         tblCurrentReportStatus INNER JOIN
                      tblFDMIVouchers ON tblCurrentReportStatus.CaseNum = tblFDMIVouchers.CaseNum INNER JOIN
                      tblCase ON tblCurrentReportStatus.CaseNum = tblCase.CaseNum INNER JOIN
                      tblAutopsy ON tblCurrentReportStatus.CaseNum = tblAutopsy.CaseNum INNER JOIN
                      tblCMEPers ON tblCase.FDMI = tblCMEPers.PersNum AND tblCMEPers.PersType = 'FDMI'
WHERE     (tblCurrentReportStatus.PRODStatus LIKE '%No report received%') AND (tblCurrentReportStatus.FieldExternalStatus LIKE '%No report received%') AND
                      (tblCMEPers.PersType = 'FDMI') AND (tblCurrentReportStatus.PRODStatus NOT LIKE '%Report received%') AND
                      (tblCurrentReportStatus.PRODStatus NOT LIKE '%No report required%') AND (tblCurrentReportStatus.FieldExternalStatus NOT LIKE '%Report received%')
                       AND (tblCurrentReportStatus.FieldExternalStatus NOT LIKE '%No Report required%')
ORDER BY tblCMEPers.PersNameLast

92 views and no answers...how depressing.

:(
Gee
- Reply
- Quote
Sunday, February 10, 2013 10:14 PM

0
Hi , you need to look careful after the AND , OR operators.

Because a>10 or B=5 AND C<3 will assure nothing in regard which values will be given, some think it will never return C<3 but it can exist given the assertion.

Please look carefully after the placement of the ()'s as this will determine the evaluation order!
- Edited by giovi2012 Sunday, February 10, 2013 10:15 PM
- Reply
- Quote
Monday, February 11, 2013 5:21 AM

0
With the way you have your predicates organized any row that meets

(tblFDMIVouchers.PaidStatus = 0)

will be returned, and everything else will be ignored because you are using OR. The same goes for everywhere else you are using OR. You need to use brackets to group your predicates in the way you want.

This is basic SQL - and this is a reporting services forum, not a sql forum.

Try hunting down Joe Celko in the Transact-SQL forum, I'm sure he'd be happy to help!

WHERE     (tblCurrentReportStatus.PRODStatus = 'No report received') AND (tblCMEPers.PersType = 'FDMI') AND
                      (tblCurrentReportStatus.PRODStatus <> 'Report received') OR
                      (tblCurrentReportStatus.PRODStatus <> 'No report required') OR
                      (tblFDMIVouchers.PaidStatus = 0) OR
                      (tblCurrentReportStatus.FieldExternalStatus = 'No report received') OR
                      (tblCurrentReportStatus.FieldExternalStatus <> 'Report received') OR
                      (tblCurrentReportStatus.FieldExternalStatus <> 'No Report required')
ORDER BY tblCMEPers.PersNameLast

Thanks! Josh Ash
- Edited by Josh Ashwood Monday, February 11, 2013 5:23 AM
- Reply
- Quote
Monday, February 11, 2013 4:00 PM

0
I was refered to you by Josh Ashwood on the Reporting Services Forum. I'm forever getting on the wrong forum, sorry...my question is:

Can anyone tell me why this code is ignoring the <>? I have tried using "Like" and "Not Like" and it still won't work.

It is including the records that contain "No report required" and "Report received"

SELECT     tblCMEPers.PersNameLast, tblCMEPers.PersNum, tblCurrentReportStatus.CaseNum, tblCurrentReportStatus.PRODStatus,
                      tblFDMIVouchers.PaidStatus, tblCase.FieldInvestigator, tblFDMIVouchers.Mileage, tblCase.CaseType, tblAutopsy.ExamType, tblCase.FDMI,
                      tblCase.NameLast, tblCase.NameFirst, tblCase.NameMiddle, tblCMEPers.PersAddr, tblFDMIVouchers.PaidStatusDate,
                      tblCurrentReportStatus.PRODStatusDate, tblCase.PrnByDate, tblCurrentReportStatus.FieldExternalStatus, tblCMEPers.PersCity, tblCMEPers.PersState,
                      tblCMEPers.PersZip, tblCMEPers.PersType
FROM         tblCurrentReportStatus INNER JOIN
                      tblFDMIVouchers ON tblCurrentReportStatus.CaseNum = tblFDMIVouchers.CaseNum INNER JOIN
                      tblCase ON tblCurrentReportStatus.CaseNum = tblCase.CaseNum INNER JOIN
                      tblAutopsy ON tblCurrentReportStatus.CaseNum = tblAutopsy.CaseNum INNER JOIN
                      tblCMEPers ON tblCase.FDMI = tblCMEPers.PersNum
WHERE     (tblCurrentReportStatus.PRODStatus = 'No report received') AND (tblCMEPers.PersType = 'FDMI') AND
                      (tblCurrentReportStatus.PRODStatus <> 'Report received') OR
                      (tblCurrentReportStatus.PRODStatus <> 'No report required') OR
                      (tblFDMIVouchers.PaidStatus = 0) OR
                      (tblCurrentReportStatus.FieldExternalStatus = 'No report received') OR
                      (tblCurrentReportStatus.FieldExternalStatus <> 'Report received') OR
                      (tblCurrentReportStatus.FieldExternalStatus <> 'No Report required')
ORDER BY tblCMEPers.PersNameLast

Gee
- Merged by Naomi NMicrosoft Community Contributor, Moderator Tuesday, February 12, 2013 1:56 PM Same question
- Reply
- Quote
Monday, February 11, 2013 4:01 PM

0

Thanks, I'll do that!

I'm forever getting the wrong forum....sorry about that.
Gee
- Reply
- Quote
Monday, February 11, 2013 4:10 PM

0

Hi Greta,

It looks like it's because of the OR's.

Are you sure you need OR and not AND?
My Blog
- Reply
- Quote

Monday, February 11, 2013 4:14 PM

Moderator

Try:

SELECT     tblCMEPers.PersNameLast, tblCMEPers.PersNum, RS.CaseNum, RS.PRODStatus,
                      V.PaidStatus, tblCase.FieldInvestigator, V.Mileage, tblCase.CaseType, tblAutopsy.ExamType, tblCase.FDMI,
                      tblCase.NameLast, tblCase.NameFirst, tblCase.NameMiddle, tblCMEPers.PersAddr, V.PaidStatusDate,
                      RS.PRODStatusDate, tblCase.PrnByDate, RS.FieldExternalStatus, tblCMEPers.PersCity, tblCMEPers.PersState,
                      tblCMEPers.PersZip, tblCMEPers.PersType
FROM         tblCurrentReportStatus RS INNER JOIN
                      tblFDMIVouchers V ON RS.CaseNum = V.CaseNum INNER JOIN
                      tblCase ON RS.CaseNum = tblCase.CaseNum INNER JOIN
                      tblAutopsy ON RS.CaseNum = tblAutopsy.CaseNum INNER JOIN
                      tblCMEPers ON tblCase.FDMI = tblCMEPers.PersNum
WHERE RS.PRODStatus = 'No report received' AND tblCMEPers.PersType = 'FDMI' AND
                      (RS.PRODStatus NOT IN ('Report received', 'No report required') OR
                      V.PaidStatus = 0 OR
                      RS.FieldExternalStatus = 'No report received' OR
                      RS.FieldExternalStatus NOT IN ('Report received','No Report required'))
ORDER BY tblCMEPers.PersNameLast

I attempted to re-write your query but the parenthesis you used didn't make it clear, so I am confused in the OR conditions. I suggest you to start from simplifying the query by using aliases instead of table names (as I did for two tables) and using NOT IN (...) instead of <> OR <> as it will make the query easier to understand and maintain. Also, don't add extra parenthesis that only make the query harder to understand, but use them to put logical conditions together.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Monday, February 11, 2013 4:14 PM

0

Hello,

Try changing the Where clause to :-

WHERE     (tblCurrentReportStatus.PRODStatus = 'No report received') AND (tblCMEPers.PersType = 'FDMI') AND

(                     (tblFDMIVouchers.PaidStatus = 0) OR
                      (tblCurrentReportStatus.FieldExternalStatus = 'No report received') OR
                      (tblCurrentReportStatus.FieldExternalStatus NOT IN ('No Report required','Report received')))
- Reply
- Quote
Monday, February 11, 2013 6:46 PM

0

Hi GretaF

The issue seems to be in the where clause. Take a minute and really consider what you are asking SQL Server.

If you are asking from a column perspective say I want the color of the car to be red and blue. The same item can't be red and blue so you would put WHERE RED OR BLUE.

Also the order of precedence is important. To me it seems like the last three conditions should again be wrapped in parenthesis (three conditions) and you might want to use an AND between this and (the rest of the conditions). Provide data, table structure and we can probably whip up something for you.
Pérez
- Reply
- Quote
Tuesday, February 12, 2013 1:46 AM

0

What I'm trying to ask it is like this:

tblCMEPers.PersType should have "FDMI"

tblFDMIVouchers.PaidStatus needs to be false, its a true/false field.

There is one table named tblCurrentReportStatus

and two fields named PRODStatus and FieldExternalStatus

The two Fields I'm querying should have "No report received" and should not have either 'No report required' or 'Report Received'

It seems like a simple question.

I'm working in Visual Studio.

I tried this, according to suggestions, and it still doesn't work.

WHERE     (tblCMEPers.PersType = 'FDMI') AND (tblFDMIVouchers.PaidStatus = 0) AND (tblCurrentReportStatus.FieldExternalStatus = 'No report received' OR
                      tblCurrentReportStatus.FieldExternalStatus NOT IN ('Report received', 'No report required')) OR
                      (tblCurrentReportStatus.PRODStatus = 'No report received') OR
                      (tblCurrentReportStatus.PRODStatus NOT IN ('Report received', 'No Report required'))

Gee
- Reply
- Quote

Tuesday, February 12, 2013 2:32 AM

Moderator

I think it should be:

WHERE     tblCMEPers.PersType = 'FDMI' AND tblFDMIVouchers.PaidStatus = 0 AND 
(tblCurrentReportStatus.FieldExternalStatus = 'No report received' OR 
                      tblCurrentReportStatus.FieldExternalStatus NOT IN ('Report received', 'No report required')) AND
                      (tblCurrentReportStatus.PRODStatus = 'No report received' OR
                     tblCurrentReportStatus.PRODStatus NOT IN ('Report received', 'No Report required'))

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Marked As Answer by GretaF Thursday, February 14, 2013 3:51 PM

Thursday, February 14, 2013 11:10 PM

0

The short answer is that your use of ORs is wrong. Look at the brackets. The real answer is that your design is seriously flawed.

Using the silly “tbl-” prefix is such a bad practice it has a name; “tibling” and we do not do it. I need to write an article on the history of this. It goes back to the old tape file operating systems day when we had to encode metadata.

Most of the other names are good and only need little touch up to get to ISO-11179 Standards. But you have a huge design error! A “<something>_status” is an attribute not an entity the way you have it. It is measured ON a nominal scale, which is usually short and stable; that means we put it in a CHECK() constraint. If the list is long or volatile, then we use a REFERENCES constraint.

The singular “Case” and “Autopsy” say that you have one case and have done one autopsy; tables are sets so their names are plural or collective nouns. Where are the reports? You have a table for the current report status of these missing report. But a status is an attribute cannot exist without an entity! And why is a current status totally different thing from a plain old status?

The infixed join syntax hides some redundant predicates; think about this pair:

AND R.prod_status = 'no report received'
AND R.prod_status <> 'report received'

In a correctly designed schema, you would have a state transition constraint on teh report and never even think this is possible. Read:
http://www.simple-talk.com/sql/t-sql-programming/state-transition-constraints/

My guess is that you want to tie a report to Cases and Autopsies via a case_nbr. I cannot see what the voucher are here. But we have no idea about the cardinality of the tables. 1:1, 1:m or what?

My guess, without DDL or specs would be more like this:

SELECT R.person_last_name, R.pers_nbr, R.case_nbr, R.prod_status,
C.field_investigator_id,
C.case_type, A.exam_type, C.fdmi, C.pers_last_name,
C.pers_first_name, C.pers_middle_name, R.pers_addr,
R.prod_status_date, C.prnby_date,
R.field_external_status, R.pers_city_name, R.pers_state_code,
R.pers_zip, R.pers_type
FROM Something_Reports AS R,
Cases AS C,
Autopsies AS A
WHERE R.case_nbr = C.case_nbr
AND R.case_nbr = A.case_nbr
AND R.pers_nbr = C.fdmi_pers_nbr
AND R.current_report_status = 'no report received'
AND R.pers_type = 'fdmi';

I am trying to trim it down to the minimal amount of data. Can we get specs and DDL?

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
- Reply
- Quote

Ignoring Does Not Contain

All Replies

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support by product

Other support links

Not an IT pro?