SQL Case Expression Syntax

SQL Case Expression Syntax?

The complete syntax depends on the database engine you're working with:

For SQL Server:

CASE case-expression
    WHEN when-expression-1 THEN value-1
  [ WHEN when-expression-n THEN value-n ... ]
  [ ELSE else-value ]
END

or:

CASE
    WHEN boolean-when-expression-1 THEN value-1
  [ WHEN boolean-when-expression-n THEN value-n ... ]
  [ ELSE else-value ]
END

expressions, etc:

case-expression    - something that produces a value
when-expression-x  - something that is compared against the case-expression
value-1            - the result of the CASE statement if:
                         the when-expression == case-expression
                      OR the boolean-when-expression == TRUE
boolean-when-exp.. - something that produces a TRUE/FALSE answer

Link: CASE (Transact-SQL)

Also note that the ordering of the WHEN statements is important. You can easily write multiple WHEN clauses that overlap, and the first one that matches is used.

Note: If no ELSE clause is specified, and no matching WHEN-condition is found, the value of the CASE expression will be NULL.

SQL: CASE expression with declared variable

The result of the CASE expression must be assigned to the variable, but it is not allowed in the same statement to return any columns from the table.

So this:

SELECT  
  @CALCULATED_FIELD = CASE 
    WHEN field2 = '1' THEN field1 
    ELSE 'ALL' 
  END 
FROM table1

is syntactically valid, although it will assign to the variable finally, the result of the CASE expression of the last row fetched.

By the name of the variable, I suspect that you want a 3d column which will be calculated by CASE:

SELECT field1, field2, 
  CASE 
    WHEN field2 = '1' THEN field1 
    ELSE 'ALL' 
  END calculated_field
FROM table1

See a simplified demo.

Setting Condition in Case Statement

It seems you want or, not case:

select * 
  from student 
 where (class like '%SE%' and CS_ex_date > sysdate and CS_ex_date < sysdate + 60) or
       (class like '%T%'  and IT_ex_date > sysdate and IT_ex_date < sysdate + 60)

SQL CASE statement WHEN and THEN values from SqlParameterCollection

Just wanted to note that this syntax error probably also wouldn't have occurred if you'd used verbatim strings:

    new SqlCommand(@"
UPDATE [Table] 
SET 
  Column = CASE 
    WHEN Column = @Column1 THEN @Column2 
    ELSE Column 
  END"
    , connection);

I tend to stick all the sql so it starts from indent level 0, and using the @ string means it can be formatted how I like an sql to be without any interim c# making it messy.

If you're building a variable number of cases, string interpolation might also tidy things up and you can have an interpolated @ string by prefixing $@ - I remember which way round they go by "Microsoft are polite and didn't want to fill people's code with @$ (ass)" ;)

var whens = "WHEN...";
for(...){
  whens += ...
  parameters.Add(..)
}

new SqlCommand($@"
UPDATE [Table] 
SET 
  Column = CASE 
    {whens}
    ELSE Column 
  END
", connection);

And of course final debugging tip; if you build an sql dynamically and you get a syntax error, pause in the debugger and look at the command text just before you run it; point to the Command variable, open the tooltip, click the magnifying glass next to CommandText property to see the string as is in a notepad style window, with new lines etc as actual new lines not as \n that the debug tooltip shows - it's a lot easier to spot syntax errors in this visualizer

_{image courtesy of https://gunnarpeipman.com/ef-core-toquerystring/amp/}

how to use CASE statement in MYSQL to select a row from results based on condition?

You description is really confusing.
Anyways, I have attempted to rewrite the query.
Instead of using

AND s2.rid IN (
                    SELECT  s1.rid

I have used a self join with the same stop table and alias it as s1 and used the join condition as s2.rid = s1.rid and s1.place = 'place1'

$sql="SELECT s2.stopno, s2.rid, b.busno, s2.pid, b.buskey, r.path,
        IF(s1.pid < s2.pid, IF(r.path=0, s2.rid, null), IF(r.path=1, s2.rid,null))
    FROM `stop` s2, bus b, `route` r, place p, `stop` s1
    WHERE s2.rid = r.rid
    AND r.bid = b.bid
    AND s2.pid = p.pid
    AND s2.rid = s1.rid and s1.place = 'place1'
    AND p.place = 'place2'"

You can also simplify this further instead of using multiple IF or CASE statements.

s1.pid < s2.pid THEN (SELECT S2.rid WHERE r.path = 0) ELSE (SELECT S2.rid WHERE r.path=1 )

You can define it using r.path in (0,1)

IF(s1.pid < s2.pid), IF(r.path in (0,1),s2.rid, NULL), NULL)

SQL CASE statement with OR?

There are two forms of CASE expression, 'searched' and 'simple'. You can't use an OR with a 'simple' CASE expression, but you can with the 'searched' form:

Case  
    When OrderID = 1 Then 'Customer1'
    When OrderID = 2 Or
         OrderID = 3 Then 'Customer2'
    Else 'Unknown Customer'
End 

Or even

Case  
    When OrderID = 1 Then 'Customer1'
    When OrderID IN (2, 3) Then 'Customer2'
    Else 'Unknown Customer'
End 

---

Related Topics