SELECT CASE vs. CASE IN SQL

Question

I do not quite understand why those two different codesamples return a different value.

somehow incorrect but working syntax, returns false results, e.g it returns 0 when the comparison is done over two equal values:

(SELECT CASE 
    WHEN 
       SUM(V.IsCompatible) OVER 
          (PARTITION BY ComputerName, UserID) = ApplicationCount 
    THEN 1 ELSE 0 END
) AS CompatibleUser

The one below returns the correct values, ie. 1 when there are two equal values compared.

(CASE 
    WHEN 
       SUM(V.IsCompatible) OVER 
         (PARTITION BY ComputerName, UserID) = ApplicationCount 
    THEN 1 ELSE 0 END
) AS CompatibleUser

or even simpler:

(SELECT CASE 
    WHEN 
       X = Y 
    THEN 1 ELSE 0 END
) AS Result

X = 22 AND Y = 22 => Result = 0

(CASE 
    WHEN 
       X = Y 
    THEN 1 ELSE 0 END
) AS Result

X = 22 AND Y = 22 => Result = 1

I understand applying the correct syntax is important, and I am aware of the SELECT CASE syntax in T-SQL, but I do not understand how the first code sample is evaluated and delivering an unexpected result.

update: full query in it's context

select userapplication.username,
   computerdetails.computername,
   sum(userapplication.iscompatible) 
       over (partition by computerdetails.computername, 
                          userapplication.userid) as compatiblecount,
   userapplication.applicationcount,
   ( case
       when sum(userapplication.iscompatible) 
           over (partition by 
           computerdetails.computername, 
           userapplication.userid) <> userapplication.applicationcount 
       then 0
       else 1
     end 
   ) as usercomputeriscompatible
from   computerdetails
   right outer join usercomputer
     on computerdetails.computerid = usercomputer.computerid
   right outer join userapplication
     on usercomputer.gebruikerid = userapplication.userid 

so userComputerIsCompatible is the result in question here

Answer 1

I think the reason for this behavior is the next one: the expressions like (SELECT ...) are considered to be sub-queries even they don't have FROM clause. Is assume the source of data for these (false) "sub-queries" is only the current row. So, (SELECT expression) is interpreted as (SELECT expression FROM current_row) and (SELECT SUM(iscompatible)OVER(...)) is executed as (SELECT SUM(iscompatible)OVER(current_row)).

Argument: analyzing execution plan for (SELECT SUM(IsWeb) OVER(PARTITION BY OrderDate) [FROM current_row]) expression

I see a Constant Scan (Scan an internal table of constants) operator instead of Clustered Index Scan before Segment and Stream Aggregate ([Expr1007] = Scalar Operator(SUM(@OrderHeader.[IsWeb] as [h].[IsWeb]))) operators. This internal table (Constant Scan) is constructed from current row.

Example (tested with SQL2005SP3 and SQL2008):

DECLARE @OrderHeader TABLE
(
     OrderHeaderID  INT IDENTITY PRIMARY KEY
    ,OrderDate      DATETIME NOT NULL
    ,IsWeb          TINYINT NOT NULL --or BIT
);
INSERT  @OrderHeader 
SELECT  '20110101', 0
UNION ALL 
SELECT  '20110101', 1
UNION ALL
SELECT  '20110101', 1
UNION ALL 
SELECT  '20110102', 1
UNION ALL
SELECT  '20110103', 0
UNION ALL
SELECT  '20110103', 0;

SELECT  *
        ,SUM(IsWeb) OVER(PARTITION BY OrderDate) SumExpression_1
FROM    @OrderHeader h
ORDER BY h.OrderDate;

SELECT  *
        ,(SELECT SUM(IsWeb) OVER(PARTITION BY OrderDate)) SumWithSubquery_2
FROM    @OrderHeader h
ORDER BY h.OrderDate;

Results:

OrderHeaderID OrderDate               IsWeb SumExpression_1
------------- ----------------------- ----- ---------------
1             2011-01-01 00:00:00.000 0     2
2             2011-01-01 00:00:00.000 1     2
3             2011-01-01 00:00:00.000 1     2
4             2011-01-02 00:00:00.000 1     1
5             2011-01-03 00:00:00.000 0     0
6             2011-01-03 00:00:00.000 0     0

OrderHeaderID OrderDate               IsWeb SumWithSubquery_2
------------- ----------------------- ----- -----------------
1             2011-01-01 00:00:00.000 0     0
2             2011-01-01 00:00:00.000 1     1
3             2011-01-01 00:00:00.000 1     1
4             2011-01-02 00:00:00.000 1     1
5             2011-01-03 00:00:00.000 0     0
6             2011-01-03 00:00:00.000 0     0