CODEWITHSUNDEEP
sqloracle-databaseoracle11g
AbdulRaheem
AbdulRaheem

Reputation: 115

how to use case with count function in oracle plsql

here is the query, i want to use case statement with count function in oracle. 

  Select case when count(*) > 0 then 'doSomething' else 'doSomething'  
    from student where student_name='faizan ahmed' and student_father='ahmed' and UPPER(student_dob)=UPPER('01-FEB-19');

please help me out, using plsql code.

ORA-00905: missing keyword 00905. 00000 - "missing keyword"

Upvotes: 0

Views: 156

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

For this purpose, use exists instead:

Select (case when exists (select 1
                          from student
                          where student_name = 'faizan ahmed' and
                                student_father = 'ahmed' and
                                upper(student_dob) = upper('01-FEB-19');
             then 'doSomething'
             else 'doSomethingElse' 
       end)
from dual;

EXISTS is usually more efficient than a count, because it can stop at the first matching row instead of aggregating the whole table.

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142788

You're missing an END for CASE:

SELECT CASE WHEN COUNT (*) > 0 THEN 
                 'doSomething' 
            ELSE 'doSomething' 
       END                                  --> This
  FROM student
 WHERE     student_name = 'faizan ahmed'
       AND student_father = 'ahmed'
       AND UPPER (student_dob) = date '2019-02-01' -- No! UPPER ('01-FEB-19');

It is easier to spot if you format code you write.

Apart from that, STUDENT_DOB seems to be a date. If so, then don't compare it to a string (because '01-feb-19' IS a string) but to a date (date '2019-02-01' - it is a date literal, consists of the date keyword and yyyy-mm-dd value).

Also, it is strange that you used UPPER with that "date" string, but all your names are lowercase. Hm?

Upvotes: 0

Related Questions