?
Solved

SQL Query 'Where" clause problem.

Posted on 2012-09-14
3
Medium Priority
?
364 Views
Last Modified: 2012-09-17
I need to include only the [Status] entries as detailed in the "where [Status] in
  ('Blacklisted', 'Compatible', 'Packaged', 'Unknown', 'Unmanaged' )" statement.  Unfortunately one of the other "Status" options is "Incompatible" and that value is also being returned when I need to exclude it.  

I have only sub-basic knowledge of SQL and the DBA that wrote this for me is on vacation.  I can only imagine it is because 'compatible' is a word within 'incompatible'.

Can anyone tell me how I can modify the statement below to ONLY return the values as shown while excluding "Incompatible"?

Any assistance would be GREATLY appreciated!

------------ START SQL QUERY--------------------
USE [ALLAppDatabase]
GO

/****** Object:  View [dbo].[vwAppsAndStatusFiltered]    Script Date: 09/14/2012 12:40:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


/****** Script for SelectTopNRows command from SSMS  ******/
CREATE view [dbo].[vwAppsAndStatusFiltered]
as
SELECT
            [AppName]
            ,[Status]
            ,[Notes]
  FROM [ALLAppDatabase].[dbo].[AppsAndStatus]
  where [Status] in
  ('Blacklisted', 'Compatible', 'Packaged', 'Unknown', 'Unmanaged' )
 
--  Status
--Compatible--
--Packaged --
--Incompatible
--Retired
--Unknown
--Unmanaged
--Blacklisted --
--In Remediation
--Excluded ?

GO

------------ END SQL QUERY--------------------
0
Comment
Question by:tneubauertocg
3 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 525 total points
ID: 38400312
>because 'compatible' is a word within 'incompatible'.
This wouldn't be the case as I don't see the LIKE keyword with any wildcards ( % ).

The WHERE clause as written should limit the rows to what you have in the IN block.

Some possibilities:
The table [ALLAppDatabase].[dbo].[AppsAndStatus] refers to something other than what you are expecting.
The column status refers to something other than what you are expecting.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 525 total points
ID: 38402031
I confirm that IN() will only return values that match exactly.
I presume you have some other conditions, eventually ORed in your WHERE clause, that result in this behavior
0
 

Author Closing Comment

by:tneubauertocg
ID: 38405456
After reading your comments I now believe it has to do with some inner joins on some other tables.  My DBA is back today so I will confirm.

Thanks!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. A while ago I wrote an article called Chart Optimization Tips (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Chart-Optimization-Tips.html).  This article explained how …
Introduction: This article is aimed at report developers who are used to developing reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes. It demonstrates how to build a report using SQL Ser…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month16 days, 4 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question