CODEWITHSUNDEEP
sqloracle-databaseselectcase
Nithin Gowda
Nithin Gowda

Reputation: 131

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"

Upvotes: 0

Views: 316

Answers (3)

Jayanti Lal
Jayanti Lal

Reputation: 1185

Syntax for case is 

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

so your code should look like this..

SELECT review.comments AS comments, 
           resort.resortid, 
           resort.resortname, 
           CASE
             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;

Also just for your information, The comma between the two tables signifies a CROSS JOIN.

So your query is equivalent to:

select * from
FROM   review
       CROSS JOIN resort

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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;

Upvotes: 3

Mureinik
Mureinik

Reputation: 311308

You're mixing two variants of the case syntax. If you're going to supply conditions (not just values), you shouldn't have an expression following the case keyword:

case -- review.comments should be removed from here
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

Upvotes: 1

Related Questions