Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212

    Unanswered: Need help on a select...where case statement.

    I have 2 variables, 1 is "from_range", and the other is "to_range".
    Which is selected by the user from the web page.

    which is something like
    1-100
    101-200
    201-300
    300-above


    I'm having problems with fulfilling the last choice, which is anything above 300, I just can't get it to work in a case statement.

    my code so far:

    Code:
    select * from table
    where   case when @FromRange=0 then ''  
               else cast(price as int)end between
               @FromRange and @ToRange
    now how can I make the case statement work for the last option ( above 300).........

    Thanks for your help....
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  2. #2
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    ooh..I forgot to mention, when the user selects 300 above,
    @Fromrange=300 and @Torange=0
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    What about this draft?


    declare @from int,@to int,@to_ int

    set @from = 1
    set @to = 0
    set @to_ = 100000000 -- something very big

    SELECT id
    FROM sysobjects
    where id between
    case when @from<>0 then @from else 0 end
    and case when @to<>0 then @to else @to_ end

  4. #4
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    thanks for your idea snail,
    I guess that would solve it, but there is still a danger if the data is above
    the @to_ value.

    I was thinking of something like .....'tablevalue > @to'

    Is it impossible ?
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  5. #5
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    Re: Need help on a select...where case statement.

    Originally posted by Patrick Chua
    I have 2 variables, 1 is "from_range", and the other is "to_range".
    Which is selected by the user from the web page.

    which is something like
    1-100
    101-200
    201-300
    300-above


    Thanks for your help....
    case
    when val < 1 then dosomething
    when val < 101 then dosomething
    when val < 201 then dosomething
    else dosomething end

    SQL Server user a lazty evavluation for case stmts, so it will try them in order and stop when one evaluates to true.

  6. #6
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Check this one:

    declare @from int,@to int

    set @from = 1000
    set @to = 0

    SELECT id
    FROM sysobjects
    where id between
    case when @from<>0 then @from else id end
    and case when @to<>0 then @to else id end
    Last edited by snail; 09-04-03 at 15:51.

  7. #7
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    Re: Need help on a select...where case statement.

    Originally posted by craigmc
    case
    when val < 1 then dosomething
    when val < 101 then dosomething
    when val < 201 then dosomething
    else dosomething end

    SQL Server user a lazty evavluation for case stmts, so it will try them in order and stop when one evaluates to true.
    whoops, i didnt take that out far enough. shoul be:

    case
    when val < 1 then dosomething
    when val < 101 then dosomething
    when val < 201 then dosomething
    when val < 301 then dosomething
    else dosomething end

  8. #8
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Need help on a select...where case statement.

    I have another idea. Instead of changing the bounderies, you may consider to change your TableValue.

    Assume that @FROM and @TO are your numeric bouderies
    0 - 100
    101 - 200
    201 - 300
    301 - 0 (= the numeric code for above)

    Your statement is normally:
    SELECT * FROM Table
    WHERE TableValue Between @FROM AND @TO

    You have to change the WHERE clause as
    WHERE TableValue >= @FROM AND case when @TO=0 THEN @TO ELSE TableValue END <= @TO

    That should do your job! Cheers
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  9. #9
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    Re: Need help on a select...where case statement.

    Or, use the maxint size for the 'above' value. In SQLServer 7, an integer can't be larger than 2,147,483,647

    So, you can use that as the above value. You wont run into a case where you get a larger number since the datatype doesn't support anything bigger.

  10. #10
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    maybe it's just me, but wooow...cool solution doktorblue...
    now why didn't I think of that

    Thanks to Snail and Craigmc too for your opinions...
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  11. #11
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Thanks, Patrick, you made my day!
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Another flavor:

    WHERE TableValue Between @FROM and isnull(nullif(@TO,0), TableValue)

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •