You are here:

Oracle/Having problem in making case statement

Advertisement


Question
Hi,
   Hope you are at good health. I am new to SQL. I am trying to make a query in which i have to select records based on user input. If user input half yearly, then i need to ask user to input date. Then check whether the inputted date lies between the 1st half or second half of year and then extract those records.

If user input monthly then i have to select records of that month only. Here what i did but i am getting error. Here is my query that i am trying to make.

select LOSA_APP.app_ref_no AS "App.Ref.No."
from
   losa_app LOSA_APP
where
   LOSA_APP.app_status in ('A','R')
and
case :report
   when 'halfYearly' Then
        case :yearlyDate
         when extract(month from to_date(:yearlyDate)) <= 6 then
         LOSA_APP.appr_dt between '1-JAN-' || extract(year from :yearlyDate ) and '30-JUN-' || extract(year from :yearlyDate )
         when extract(month from to_date(:yearlyDate)) >= 7 then
         LOSA_APP.appr_dt between '1-JUL-' || extract(year from :yearlyDate )  and '30-DEC-' || extract(year from :yearlyDate )
         end
   when 'monthly' Then
         extract(month from LOSA_APP.appr_dt) = extract(month from :monthlyDate) and
         extract(year from LOSA_APP.appr_dt) = extract(year from :monthlyDate) end

I am getting error in the case statement part

ORA-00905: missing keyword
00905. 00000 -  "missing keyword"
*Cause:    
*Action:
Error at Line: 10 Column: 50

Line 10 is when to_char(:yearlyDate, 'mm') <= 6 then. What i am doing wrong here ?

Thanks

Answer
Hi Basit,

Try using to_date for all the :yearlyDate and :monthlyDate variables wherever missing.

Or you can try this -

select LOSA_APP.app_ref_no AS "App.Ref.No."
from
  losa_app LOSA_APP
where
  LOSA_APP.app_status in ('A','R')
and
case when (:report = 'halfYearly') Then
       case when (extract(month from to_date(:yearlyDate)) <= 6) then
         LOSA_APP.appr_dt between '1-JAN-' || extract(year from to_date(:yearlyDate) ) and '30-JUN-' || extract(year from to_date(:yearlyDate) )
         when (extract(month from to_date(:yearlyDate)) >= 7) then
         LOSA_APP.appr_dt between '1-JUL-' || extract(year from to_date(:yearlyDate) )  and '30-DEC-' || extract(year from to_date(:yearlyDate) )
       end
    when (:report = 'monthly') Then
         extract(month from LOSA_APP.appr_dt) = extract(month from to_date(:monthlyDate)) and
         extract(year from LOSA_APP.appr_dt) = extract(year from to_date(:monthlyDate))
    end


Hope this helps

Regards

Suchitra

Oracle

All Answers


Answers by Expert:


Ask Experts

Volunteer


Suchitra Joshi

Expertise

I can answer questions regarding SQL, PL/SQL, Procedures, Functions, Triggers, SQL Loader, Oracle Forms, Oracle Reports, and some basic dba and performance tuning activities.

Experience

15+ years of Oracle PL/SQL Development

Education/Credentials
B.Sc (Electronics), Diploma in Computer Applications (DCA)

Awards and Honors
Oracle PL/SQL Developer Certified Associate (OCA)
Oracle Database: SQL Certified Expert
Brainbench certifications in Oracle Administration, PL/SQL, Developer 2000

©2016 About.com. All rights reserved.