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,
x
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”
Advertisement
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;