SQL CASE Expression

I need to write a sql statement(Oracle) where I can withdraw data from two diff tables and check for a condition and return a certain string. My statement goes like this,

Select review.comments as comments,resort.resortid,resort.resortname 
case review.comments 
when resort.starrating>=4.5 and resort.starrating<5 then 'Excellent'
when resort.strarating>=4 and resort.starrating<4.5 then 'Great'
else 'Good'
end
from review, resort 
order by resort.resortid;

When I run this I get a error: “FROM keyword not found where expected”and points to the c in line 2. and if I change the from to line 2, it gives error: “SQL command not properly ended”. and points to line 3 r in “case review.comments”

Answer

You need a JOIN, aggregation, and to fix the CASE expression syntax. I assume you want something like this:

Select res.resortid, res.resortname, 
       (case when avg(rev.starrating) >= 4.5 then 'Excellent'
             when avg(rev.starrating) >= 4 then 'Great'    
            else 'Good'
        end) as category
from review rev join
     resort res
     on rev.resortid = res.resortid
group by res.resortid, res.resortname 
order by res.resortid;