Search code examples
sqloraclecaseboolean-expression

Oracle CASE and BOOLEAN Numeric Expressions

Not sure why this does not work... Seems as though a >= b is a boolean expression. The RDMS is complaining about ">=3" saying "missing keyword". I followed syntax here

This works:

WHEN count(movie_num) 3 THEN 'Most Popular'

This does not:

WHEN count(movie_num) >=3 THEN 'Most Popular'

Probably missing something obvious so my apologies if that's the case.

Full query

select movie_genre as "Movie Genre", 
count(movie_num) as "Number of Movies",
CASE count(movie_num)
    WHEN count(movie_num) >=3 THEN 'Most Popular'
    WHEN count(movie_num) >= 2 THEN 'Popular'
    ELSE 'Normal' END AS "Popularity Rating"
from movie 
group by movie_genre
order by count(movie_num) desc;
Solution

  • Try this,

    changed CASE (movie_num) WHEN count(movie_num) to CASE WHEN count(movie_num)

    select movie_genre as "Movie Genre", 
count(movie_num) as "Number of Movies",
     CASE WHEN count(movie_num) >=3 THEN 'Most Popular'
          WHEN count(movie_num) >= 2 THEN 'Popular'
     ELSE 'Normal' END AS "Popularity Rating"
from movie 
group by movie_genre
order by count(movie_num) desc;