+ Reply to Thread
Results 1 to 6 of 6

greater than, less than in a CASE statement

  1. 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

  2. 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:4D9D0644-C57C-49B4-B4A4-1DD4A3976E50@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



  3. 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



  4. 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:4D9D0644-C57C-49B4-B4A4-1DD4A3976E50@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

    >


  5. 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
    >
    >
    >


  6. 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.


+ Reply to Thread