SELECT CASE and CASE IN SQL

Question

I do not quite understand why these two different codes return a different value.

incorrect, but working syntax returns false results, for example, it returns 0 when the comparison is performed on two equal values:

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

The following are the correct values , i.e. 1 when two equal values ​​are 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 that the correct syntax is applied , and I know the syntax of SELECT CASE in T-SQL, but I do not understand how the sample of the first code is evaluated and bring an unexpected result.

update: full request in 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 we are talking about here.

Answer 1

I think the reason for this behavior is as follows: expressions of the type (SELECT ...) are considered subqueries, even if they do not have a FROM . It is assumed that the data source for these (false) "subqueries" is only the current row. So, (SELECT expression) interpreted as (SELECT expression FROM current_row) , and (SELECT SUM(iscompatible)OVER(...)) is executed as (SELECT SUM(iscompatible)OVER(current_row)) .

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

I see Constant Scan (scanning the internal constant table) instead of the Clustered Index Scan to Segment and Stream Aggregate ( [Expr1007] = Scalar Operator(SUM(@OrderHeader.[IsWeb] as [h].[IsWeb])) ). This internal table ( Constant Scan ) is built from the 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 

Answer 2

I tried your code and I get the same results for both queries. Here is the code I tried:

 DECLARE @X INT = 22 DECLARE @Y INT = 22 SELECT (SELECT CASE WHEN @X = @Y THEN 1 ELSE 0 END ) AS Result SELECT (CASE WHEN @X = @Y THEN 1 ELSE 0 END ) AS Result 

Result 1 and 1

I ran this on SQL Server 2008 R2