CASE WHEN not working

SQL Apprentice Question
I'm having a problem with a SQL query that uses a bunch of "case when"
statements. I am fairly new at this, so bear with me.


We are trying to determine how many people have the different versions
of antivirus software on their machines. In the instance where they
have none of the three versions that have been available on our
network, I am trying to create a field called "noVersions" that will
populate by evaluating a CASE WHEN statement that (I think) says, if
there's no version10, no version9 or no version8, then put a "1"in the
noVersions column.


Here is the entire query:


SELECT
CASE WHEN Version10.version10 = 1 then 1 Else 0 end as version10,
CASE WHEN Version9.version9 = 1 then 1 Else 0 end as version9,
CASE WHEN Version8.version8 = 1 then 1 Else 0 end as version8,
CASE WHEN Version10.version10 = 0 and Version9.version9 = 0 and
Version8.version8 = 0 then 1 Else 0 end as noVersions,
CASE WHEN Version10.version10 = 1 and Version9.version9 = 1 and
Version8.version8 = 1 then 1 Else 0 end as allVersions,
CASE WHEN GIManaged.ResourceID IS NULL THEN 0 Else 1 end as
ManagedByGI,
CASE WHEN GIManaged_LAB.ResourceID IS NULL THEN 0 Else 1 end as
GIManaged_LAB,
CASE WHEN Healthy.ResourceID IS NULL THEN 0 Else 1 end as Healthy,
v_R_System.Netbios_Name0, v_R_System.User_Name0,
v_R_System.Operating_System_Name_and0,
eCoreRev.eCoreRevision,
lastScanDate.lastScanDate, Obsolete0 , Resource_Domain_OR_Workgr0 ,
AD_Site_Name0
FROM v_R_System LEFT OUTER JOIN
(SELECT DISTINCT ResourceID
FROM v_FullCollectionMembership
WHERE (CollectionID = 'MRK00DAA'))
GIManaged_LAB ON v_R_System.ResourceID = GIManaged_LAB.ResourceID LEFT
OUTER JOIN
(SELECT DISTINCT ResourceID
FROM v_FullCollectionMembership
WHERE (CollectionID = 'MRK011FB'))
Healthy ON v_R_System.ResourceID = Healthy.ResourceID LEFT OUTER JOIN
v_GS_LastSoftwareScan lastScanDate ON
v_R_System.ResourceID = lastScanDate.ResourceID LEFT OUTER JOIN
(SELECT DISTINCT ResourceID
FROM v_FullCollectionMembership
WHERE (CollectionID = 'MRK00D4F'))
GIManaged ON v_R_System.ResourceID = GIManaged.ResourceID LEFT OUTER
JOIN
(SELECT ResourceID, Data0 AS
eCoreRevision
FROM v_GS_eCore_XP_Build0
WHERE (Property0 = 'eCoreRevision'))
eCoreRev ON v_R_System.ResourceID = eCoreRev.ResourceID LEFT OUTER JOIN
(SELECT ResourceID, 1 AS version10
FROM v_GS_eCore_XP_Softwa0
WHERE (Application0 LIKE'SAV 10
Client%')) Version10 ON
v_R_System.ResourceID = Version10.ResourceID LEFT
OUTER JOIN
(SELECT ResourceID, 1 AS version9
FROM v_GS_eCore_XP_Softwa0
WHERE (Application0 LIKE'SAV 9
Client%')) Version9 ON
v_R_System.ResourceID = Version9.ResourceID LEFT
OUTER JOIN
(SELECT ResourceID, 1 AS version8
FROM v_GS_eCore_XP_Softwa0
WHERE (Application0 LIKE'SAV 8%'))
Version8 ON
v_R_System.ResourceID = Version8.ResourceID
WHERE (v_R_System.Operating_System_Name_and0 LIKE '%5.1%') And
Obsolete0 NOT LIKE '1'


The problem is in the FOURTH "case when" statement. when I analyze the
results, there are several instances where all three versions are
zeroes but the 'noversions' column does not indicate a one.
Conversely, the fifth line uses an almost identical "case when"
statement and it DOES work correctly.


Am I missing something here?



Celko Answers
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

What you did post looks awful. Table and columns with the same name.
The same subquery appearing over and over4. The construction of flags
in convoluted SQL. Etc.


My guess is that the whole thign can be reduced to something like this:


SELECT VR.resource_id,
CASE WHEN G.version_nbr = 10 THEN 1 ELSE 0 END AS ver_10,
CASE WHEN G.version_nbr = 9 THEN 1 ELSE 0 END AS ver_9,
CASE WHEN G.version_nbr = 8 THEN 1 ELSE 0 END AS ver_8,
..
FROM V_R_System AS VR
LEFT OUTER JOIN
(SELECT DISTINCT resource_id
FROM Full_CollectionMembership
WHERE collection_id
IN ('MRK00DAA', 'MRK011FB', 'MRK00D4F')
AND ..) AS G
ON VR.resource_id = G.resource_id;


This is only an outline since we have no DDL, sample data or clear
specs. The other columns and the playing around with COALESCE seem to
be redundant once you have the flags for the three versions.



>> I'm having a problem with a SQL query that uses a bunch of "case when" statements. I am fairly new at this, so bear with me. <<


First thing to know that this is a CASE **expression** and not a
statement. You are thinking about it all wrong.


>> We are trying to determine how many people have the different versions of antivirus software on their machines. In the instance where they have none of the three versions that have been available on our network, I am trying to create a field [sic] called "noVersions" that will populate by evaluating a CASE WHEN statement that (I think) says, if there's no version10, no version9 or no version8, then put a "1"in the noVersions column. <<


Redundant columns (whcih are not fields!) are a bad SQL programming
practice. If you really need to show it, then do it is the front end.
It would also help if you were consistent in formatting your code
(reserved words in UPPERCASE all the time, data element names in
lowercase, no more camelCase, etc.)


>> The problem is in the FOURTH "case when" statement. when I analyze the results, there are several instances where all three versions are zeroes but the 'noversions' column does not indicate a one. <<


Try putting the real version of my query in the FROM clause, then use

SELECT (ver_10 + ver_9 + ver_8) AS total_versions
FROM ( ..)