CODEWITHSUNDEEP
mysqlsqldatabasedatecase
H I
H I

Reputation: 100

How to compare a value within a range, inside (case when ~~ end as) query for mysql?

this is what I got:

select 

    case when calldate >= date_add(now(), interval -1 day) 
    then ifnull(rssi,0)
    else '0' end as rssi

and I want to change this part as,

for now I get the raw data of rssi, for example any digits between 0 and 120..

but now I want to change the rssi result if the data is in between certain range.

For example, if rssi is between 0 and 20, its 1. if its between 21 and 40, its 2, kinda.

I have tried:

select 

    case when calldate >= date_add(now(), interval -1 day) 
    then case when ifnull(rssi,0) between 0 and 20 then '1' as rssi
         else when ifnull(rssi,0) between 21 and 40 then '2' as rssi
         else when ifnull(rssi,0) between 41 and 60 then '3' as rssi
         else '4' end as rssi
    else '0' end as rssi

which told me I have mysql error. Also I have tried:

select 

    case when calldate >= date_add(now(), interval -1 day) 
    then if(0 <= ifnull(rssi,0) < 20) then '1' as rssi
         else if(21 <= ifnull(rssi,0) < 40) then '2' as rssi
         else if(41 <= ifnull(rssi,0) < 60) then '3' as rssi
         else '4' end as rssi
    else '0' end as rssi

but also errored.

I'm pretty lost at how to compare my rssi within a range. Because it's inside the case (when~~else end as) query. Can anyone help me?

Upvotes: 0

Views: 279

Answers (2)

GMB
GMB

Reputation: 222582

I think that you want:

case 
    when calldate >= date_add(now(), interval -1 day) 
    then case 
        when ifnull(rssi,0) between 0 and 20 then 1
        when rssi between 21 and 40 then 2
        when rssi between 41 and 60 then 3
        else 4
    end 
    else 0 
end as rssi

Rationale:

  • case expects a series of when ... then ... constructs and a single else
  • you need ifnull(rssi,0) only when 0 is in the control range (otherwise, if it is null, it will not match anyway)
  • the column alias must be present only once, at the end of the outer case
  • returning numeric values would probably make more sense than strings

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

your aliasing should be after the case statement. then remove your else statements

select     
    case when calldate >= date_add(now(), interval -1 day) 
    then 
         case when ifnull(rssi,0) between 0 and 20 then '1'
           when ifnull(rssi,0) between 21 and 40 then '2' 
           when ifnull(rssi,0) between 41 and 60 then '3' 
           else '4' end 
    else '0' end as rssi

Upvotes: 1

Related Questions