CODEWITHSUNDEEP
sqloracle-databasedate
rawan
rawan

Reputation: 27

ORA-00907: missing right parenthesis with decode and case in oracle sql

I am putting fromdate and todate as parameter I want to get the previous month date of fromdate and when todate not the date in same month of fromdate I need to put last date of the fromdate month but if todate is date in month of fromdate I need to put it as it is, when user put for example from 1-1-2016 to 25-1-2016 I will get previous month 1-12-2015 to 25-12-2016 of the from month and I need to check the todate if it is in same month of fromdate I will keep it but if it not for example from 1-1-2016 to 25-4-2016 I will make it 30-1-2016. I am trying to use decode and case but in both am getting same error ORA-00907: missing right parenthesis

my query In case 

 select (case ( when (LAST_DAY(TRUNC(TO_DATE(:fromdate ,'DD-MM-YYYY') , 'Month')) <> (TO_DATE(:fromdate ,'DD-MM-YYYY')))  
then (DATE between ADd_MONTHS(TO_DATE(:fromdate ,'DD-MM-YYYY') , -1) and LAST_DAY(TRUNC(TO_DATE(:todate ,'DD-MM-YYYY') , 'Month')))
else (DATE between ADD_MONTHS(TRUNC(TO_DATE(:fromdate ,'DD-MM-YYYY') , 'Month'),-1) and TO_DATE(:todate ,'DD-MM-YYYY'))
 end))
 FROM  dual;

my query in decode 

select DECODE((TO_DATE(:fromdate ,'DD-MM-YYYY')) , LAST_DAY(TRUNC(TO_DATE(:fromdate ,'DD-MM-YYYY') , 'Month')) , 
(DATE  between ADd_MONTHS(TO_DATE(:fromdate ,'DD-MM-YYYY') , -1) and ADd_MONTHS(TO_DATE(:todate ,'DD-MM-YYYY') , -1)) ,
(DATE between ADD_MONTHS(TRUNC(TO_DATE(:fromdate ,'DD-MM-YYYY') , 'Month'),-1) and TO_DATE(:todate ,'DD-MM-YYYY'))
FROM  dual ;

Upvotes: 0

Views: 1939

Answers (1)

Peter M.
Peter M.

Reputation: 713

select
    to_date(:from_date) "Original From",
    to_date(:to_date) "Original To",
    case when trunc(to_date(:from_date),'MONTH') = trunc(to_date(:to_date),'MONTH')
         then add_months(to_date(:from_date),-1)
         else to_date(:from_date)
    end "New From",
    case when trunc(to_date(:from_date),'MONTH') = trunc(to_date(:to_date),'MONTH')
         then add_months(to_date(:to_date),-1)
         else add_months(trunc(to_date(:from_date),'MONTH'),1)-1
    end "New To"
from dual;

I've put to_date around all the bind variables as it was using SQL Developer strings as input.

Upvotes: 0

Related Questions