Comparison sign 'greater than' and 'equal to' in CASE SQL Statement

Question

I am new to SQL Server and this is my first post.
I am getting the message "incorrect syntax near '=' when using the Case Statement. Here is an example of my code:

  Select * , CASE 
    when a > b THEN b = a
    when c > d THEN d = c
    when e > f THEN f = e
    when g > h THEN h = g
    when i > j THEN j = i
    when k > l THEN l = k
    when m > n THEN n = m
    when o > p THEN p = o

END as value
INTO #temptable
From #atemptable

Thanks in advance for your help!

Hello MsAB2, can you first explain what it is you are trying to do? — TT., Jun 02 '19 at 04:19
I am trying to evaluate an expression where it is checking if one variable is greater than the other. if it is then set the lesser variable equal to the greater variable. Make sense? Does this answer your question? It is existing code in another language (SAS) that I am trying to redo in SQL. — MsAB2, Jun 02 '19 at 04:26
Heh, I can read that sort of, but you cannot do that in a SELECT statement. It exists only to SELECT stuff. No assigning or anything possible, that's what you have the UPDATE statement for. — TT., Jun 02 '19 at 04:30

score 1 · Answer 1 · answered Jun 02 '19 at 04:45

1

It's not clear if you want the THEN part to be an assignment or a comparison. Either way, it's not possible, the THEN part can only be an expression. I'm assuming that you wanted an assignment. It looks that you don't want to update the #atemptable, you just want the new values in #temptable. In this case, you can use separate CASE expressions, like this:

SELECT  A, CASE WHEN A > B THEN A ELSE B END AS B,
        C, CASE WHEN C > D THEN C ELSE D END AS D
END AS value
INTO #temptable
FROM #atemptable

answered Jun 02 '19 at 04:45

Razvan Socol

5,426
2
20
32

1

Pedantic dive into the documentation: A comparison, e.g. `pi >= e`, is an [expression](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/expressions-transact-sql?view=sql-server-2017) with a result data type of [`Boolean`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/comparison-operators-transact-sql?view=sql-server-2017#boolean-data-type). As the documentation states: "Unlike other SQL Server data types, a `Boolean` data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set." ... – HABO Jun 02 '19 at 13:41
... To complete the cycle the documentation for [`case`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017#arguments) offers: "_result expression_ is any valid expression." So a `case` result expression can be an equality comparison, but it can't because the `Boolean` data type can't be returned, but it is a "valid expression", but not valid in that context. Where is the "pulling my hair out" emoji? – HABO Jun 02 '19 at 13:42
Well, it would seem that `SELECT 1 WHERE CASE WHEN 1=1 THEN 1=0 ELSE 1=1 END` should work, but it doesn't. – Razvan Socol Jun 02 '19 at 17:06
It would _almost_ seem that way. The problem is that "a Boolean data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set." If it could then `select 1 > 0;` would work because a [select list](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-clause-transact-sql?view=sql-server-2017#arguments) can contain an expression. Since you can create `Boolean` expressions, but can never hold onto a `Boolean` value, it makes the documentation sound like an argument about what "is" is. – HABO Jun 02 '19 at 17:56
In my earlier comment I did not attempt to _return_ a `Boolean` expression, I was attempting to use it in the `WHERE` condition. – Razvan Socol Jun 03 '19 at 03:29
1

A `where` clause [search expression](https://learn.microsoft.com/en-us/sql/t-sql/queries/search-condition-transact-sql?view=sql-server-2017) `` can't be an `` without some other pieces/parts, e.g. a comparison to another ``, so `... where case ... end;` would not be valid. Trying your statement still results in an error in the `then` clause since the return value of the `case` expression can't be a `Boolean`, an issue that my second comment makes unclear by quoting the Gospel According To Microsoft. (I now have Microsoft Pattern Baldness.) – HABO Jun 03 '19 at 04:01
@Habo, thank you both for your feedback. I am still working on this issue and will provide an update soon. – MsAB2 Jun 03 '19 at 06:03

score 1 · Answer 2 · answered Jun 02 '19 at 05:50

Not only you need an update statement as TT wrote in the comments, you also can't use case expressions as flow control.

A case expression returns a value based on condition(s). An operation inside the Then part of a case expression is only valid when it returns a value.

You are probably looking for something like this:

UPDATE #temptable
SET B = CASE A > B Than A ELSE B END
   ,D = IIF(C > D, C, D) -- IIF is syntactic sugar for simple case expressions
 -- more of the same for all other columns

thank you for your feedback. I am still working to resolve this issue. I will provide an update once all is resolved. — MsAB2, Jun 03 '19 at 05:59

Vahid Farahmandian · Answer 3 · 2019-06-02T07:42:59.187

You need to know an important note about the CASE statement in T-SQL:

The CASE expression evaluates its conditions sequentially and stops with the first condition whose condition is satisfied

Read more here:https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017

So, based on the aforementioned tip, you CASE will not be executed as you expected, because if we assume that your first condition(a>b) is true, then the evaluation of CASE will be stopped and you will not get the other conditions results.

To solve it you need to write a CASE statement for each your conditions as below:

SELECT *,
       CASE WHEN a > b THEN b ELSE a END AS AorB,
       CASE WHEN c > d THEN d ELSE c END AS CorD,
       CASE WHEN e > f THEN f ELSE e END AS EorF,
       CASE WHEN g > h THEN h ELSE g END AS GorH,
       CASE WHEN i > j THEN j ELSE i END AS JorI,
       CASE WHEN k > l THEN l ELSE k END AS LorK,
       CASE WHEN m > n THEN n ELSE m END AS MorN,
       CASE WHEN o > p THEN p ELSE o END AS OorP
INTO #temptable
From #atemptable

Update

As you mentioned in your comment:

I am trying to evaluate an expression where it is checking if one variable is greater than the other. if it is then set the lesser variable equal to the greater variable

So the story as totally different and you should use an approach like this:

Suppose that we have a set of variables which are defined as below:Read more about Variable definition in T-SQL: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/declare-local-variable-transact-sql?view=sql-server-2017

DECLARE 
    @a INT = 2, @b INT = 1,
    @c INT = 3, @d INT = 4,
    @e INT = 6, @f INT = 5,
    @g INT = 7, @h INT = 8,
    @i INT = 10,@j INT = 9,
    @k INT = 12,@l INT = 11,
    @m INT = 13,@n INT = 14,
    @o INT = 16,@p INT = 15

To evaluate your scenario we need to write the code as below:

       SET @b= CASE WHEN @a > @b THEN @a ELSE @b END
       SET @d= CASE WHEN @c > @d THEN @c ELSE @d END
       SET @f= CASE WHEN @e > @f THEN @e ELSE @f END
       SET @h= CASE WHEN @g > @h THEN @g ELSE @g END
       SET @j= CASE WHEN @i > @j THEN @i ELSE @i END
       SET @l= CASE WHEN @k > @l THEN @k ELSE @k END
       SET @n= CASE WHEN @m > @n THEN @m ELSE @m END
       SET @p= CASE WHEN @o > @p THEN @o ELSE @o END

So if we execute the SELECT, we will see the result:

       SELECT @a,@b, ...

thank you for your feedback. I am still working on this issue and will determine if what you presented will work. I will keep you updated. — MsAB2, Jun 03 '19 at 05:57

Comparison sign 'greater than' and 'equal to' in CASE SQL Statement

3 Answers3