rawan rawan - 4 months ago 44
SQL Question

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 ;

Answer
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.