
greater than, less than in a CASE statement
Hello:
As you know, the CASE statement is SQL's version of an "if, then" statement.
Usually, the syntx for this is as follows:
CASE input_expression
WHEN when_expression THEN result_expression
ELSE else_result_expression
END
If I wanted to place a "greater than, less than" clause within such an "if,
then" statement, how would I do so?
In my statement, I want to say "If {some field} is greater than '100' and
less than '400', then {this other field} = '0' else if {some field} is
greater than '400', then {this other field} = '1' else {this other field}".
Any help as soon as possible would be most appreciated!
Thanks!
childofthe1980s

Re: greater than, less than in a CASE statement
> As you know, the CASE statement is SQL's version of an "if, then"
> statement.
> Usually, the syntx for this is as follows:
>
> CASE input_expression
> WHEN when_expression THEN result_expression
> ELSE else_result_expression
> END
CASE is more like a function rather than a statement because it returns a
scalar result. There are 2 forms of CASE: simple and searched. You listed
the simple form in your example.
> If I wanted to place a "greater than, less than" clause within such an
> "if,
> then" statement, how would I do so?
>
You can use a searched CASE syntax for more complex expressions.
CASE
WHEN when_expression >= input_expression THEN result_expression
ELSE else_result_expression
END
> In my statement, I want to say "If {some field} is greater than '100' and
> less than '400', then {this other field} = '0' else if {some field} is
> greater than '400', then {this other field} = '1' else {this other
> field}".
CASE
WHEN SomeField > 100 and SomeField < 400 THEN 0
WHEN SomeField > 400 THEN 1
END
In the above example, the result will be NULL if SomeField is less than or
equal to 100 and will also be NULL if SomeField is equal to 400 since an
equality match on the boundary condition was not included and no ELSE was
specified.
See CASE in the Books Online for details.

Hope this helps.
Dan Guzman
SQL Server MVP
"childofthe1980s" wrote in
message news:4D9D0644C57C49B4B4A41DD4A3976E50@microsoft.com...
> Hello:
>
> As you know, the CASE statement is SQL's version of an "if, then"
> statement.
> Usually, the syntx for this is as follows:
>
> CASE input_expression
> WHEN when_expression THEN result_expression
> ELSE else_result_expression
> END
>
> If I wanted to place a "greater than, less than" clause within such an
> "if,
> then" statement, how would I do so?
>
> In my statement, I want to say "If {some field} is greater than '100' and
> less than '400', then {this other field} = '0' else if {some field} is
> greater than '400', then {this other field} = '1' else {this other
> field}".
>
> Any help as soon as possible would be most appreciated!
>
> Thanks!
>
> childofthe1980s

Re: greater than, less than in a CASE statement
Hello childofthe1980s,
> Hello:
>
> As you know, the CASE statement is SQL's version of an "if, then"
> statement.
> Usually, the syntx for this is as follows:
> CASE input_expression WHEN when_expression THEN result_expression ELSE
> else_result_expression END
>
> If I wanted to place a "greater than, less than" clause within such an
> "if, then" statement, how would I do so?
>
> In my statement, I want to say "If {some field} is greater than '100'
> and less than '400', then {this other field} = '0' else if {some
> field} is greater than '400', then {this other field} = '1' else {this
> other field}".
>
> Any help as soon as possible would be most appreciated!
>
> Thanks!
>
> childofthe1980s
>
You can use the following syntax:
CASE
WHEN (field > value) THEN a
WHEN (field < value) THEN b
ELSE b
END
Jesse

Re: greater than, less than in a CASE statement
Thanks, Dan!
childofthe1980s
"Dan Guzman" wrote:
> > As you know, the CASE statement is SQL's version of an "if, then"
> > statement.
> > Usually, the syntx for this is as follows:
> >
> > CASE input_expression
> > WHEN when_expression THEN result_expression
> > ELSE else_result_expression
> > END
>
> CASE is more like a function rather than a statement because it returns a
> scalar result. There are 2 forms of CASE: simple and searched. You listed
> the simple form in your example.
>
> > If I wanted to place a "greater than, less than" clause within such an
> > "if,
> > then" statement, how would I do so?
> >
>
> You can use a searched CASE syntax for more complex expressions.
>
> CASE
> WHEN when_expression >= input_expression THEN result_expression
> ELSE else_result_expression
> END
>
> > In my statement, I want to say "If {some field} is greater than '100' and
> > less than '400', then {this other field} = '0' else if {some field} is
> > greater than '400', then {this other field} = '1' else {this other
> > field}".
>
> CASE
> WHEN SomeField > 100 and SomeField < 400 THEN 0
> WHEN SomeField > 400 THEN 1
> END
>
> In the above example, the result will be NULL if SomeField is less than or
> equal to 100 and will also be NULL if SomeField is equal to 400 since an
> equality match on the boundary condition was not included and no ELSE was
> specified.
>
> See CASE in the Books Online for details.
>
> 
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "childofthe1980s" wrote in
> message news:4D9D0644C57C49B4B4A41DD4A3976E50@microsoft.com...
> > Hello:
> >
> > As you know, the CASE statement is SQL's version of an "if, then"
> > statement.
> > Usually, the syntx for this is as follows:
> >
> > CASE input_expression
> > WHEN when_expression THEN result_expression
> > ELSE else_result_expression
> > END
> >
> > If I wanted to place a "greater than, less than" clause within such an
> > "if,
> > then" statement, how would I do so?
> >
> > In my statement, I want to say "If {some field} is greater than '100' and
> > less than '400', then {this other field} = '0' else if {some field} is
> > greater than '400', then {this other field} = '1' else {this other
> > field}".
> >
> > Any help as soon as possible would be most appreciated!
> >
> > Thanks!
> >
> > childofthe1980s
>

Re: greater than, less than in a CASE statement
Thanks, Jesse!
childofthe1980s
"Jesse Houwing" wrote:
> Hello childofthe1980s,
>
> > Hello:
> >
> > As you know, the CASE statement is SQL's version of an "if, then"
> > statement.
> > Usually, the syntx for this is as follows:
> > CASE input_expression WHEN when_expression THEN result_expression ELSE
> > else_result_expression END
> >
> > If I wanted to place a "greater than, less than" clause within such an
> > "if, then" statement, how would I do so?
> >
> > In my statement, I want to say "If {some field} is greater than '100'
> > and less than '400', then {this other field} = '0' else if {some
> > field} is greater than '400', then {this other field} = '1' else {this
> > other field}".
> >
> > Any help as soon as possible would be most appreciated!
> >
> > Thanks!
> >
> > childofthe1980s
> >
>
>
> You can use the following syntax:
>
> CASE
> WHEN (field > value) THEN a
> WHEN (field < value) THEN b
> ELSE b
> END
>
> Jesse
>
>
>

Re: greater than, less than in a CASE statement
>> the CASE statement is SQL's version of an "if, then" statement. <<
No; there is a CASE expression in SQL , which has nothing to do with
the flow of control. Since SQL is a declarative language, there is no
flow of control anyway!
>> In my statement [sic], I want to say "If {some field[sic]} is greater than '100' and less than '400', then {this other field [sic]} = '0' else if {some field} is greater than '400', then {this other field [sic] } = '1' else {this other field [sic]}". <<
You missed the fundamental concept of the language. Is this what you
meant?
UPDATE Foobar
SET x = CASE WHEN y > 100 AND y < 400 THEN 0 ELSE 1 END;
I also hope that the choice of zero and one is not for a Boolean
flag.