ORA-00907: missing right parenthesis — Strange

Question

I'm getting the error " ORA-00907: missing right parenthesis" but I've checked and all the parenthesis are there, so I'm stumped.

Am getting this error while using Select Statement. Please help. I need to use the Between to_date also to fetch the data between two particular dates.

select

 DT.ARRIVAL||','||
 DT.LOCATION_CODE||','|| nvl (decode (
                                 DT.OWNER_FLAG, 'R', 'B', 'M', 'B',
                              DT.OWNER_FLAG), 'B') OWNER_FLAG,
 DT.RECEIPT||','||
 PP.BILL_NUMBER||','||
 DT.TRANSPORT_MODE||','||
 substr (TRANSPORT_NAME,1,20)
               , TRANSPORT_TYPE||','||
 PP.PRODUCT||','||
 AP.PRODUCT_NAME||','||
 AP.PRODUCT_QTY||','||
 AP.PRODUCT_QTY_Units||','||
 DT.INSPECTOR||','||
 AP.PRODUCT_NO||','||
 AP.PRODUCT_FLAG||','||
 PP.BILL_STATUS
 from BILL_PARCEL PP,
 PRODUCT DT,
 SUPPORT AP,
 TRANSPORT V
 where PP.BILL_NUMBER = DT.BILL_NUMBER
 and  PP.BILL_NUMBER = AP.BILL_NUMBER
 and DT.BILL_NUMBER = AP.BILL_NUMBER
 and AP.YEAR = DT.YEAR
 and PP.YEAR = DT.YEAR
 and PP.PRODUCT_NAME= AP.PRODUCT_NAME
 and PP.YEAR = 2017 and DT.YEAR = 2017 and AP.YEAR=2017
 and nvl(AP.BILL_STATUS,'0') not in ('D','I','R')
 and V.TRANSPORT_ID = DT.VESS_ID
 and   TRANSPORT_MODE != 'X'
 and (  (DT.ARRIVAL        between 
 to_date(to_char(&C_FROM_DATE,'DD-MON-YY'),'DD-MON-RR')
                       and     
 to_date(to_char(&C_TO_DATE,'DD-MON-YY'),'DD-MON-RR')
 and nvl(AP.BILL_STATUS,'0') in ('U','S')
        )
 or ( TRANSFER_END between 
 to_date(to_char(&C_FROM_DATE,'DD-MON-YY') ,'DD-MON-RR')
                       and     
 to_date(to_char(&C_TO_DATE,'DD-MON-YY'),'DD-MON-RR')
 and nvl(AP.BILL_STATUS,'0') in ('P','C','A')
        )
      )
 order by decode (AP.BILL_STATUS, 'U', DT.ARRIVAL
                              ,'S', DT.ARRIVAL
                                  , TRANSFER_DATE)
,decode (TRANSPORT_MODE,'B','1','T','2','P','3',TRANSPORT_MODE);

Answer 1

change this line,

to_date(to_char(&C_TO_DATE,'DD-MON-YY'),'DD-MON-RR') 

to

to_date(to_char(&C_TO_DATE,'DD-MON-YY'),'DD-MON-RR') ))
                                                 --  ^

You are really missing right parenthesis.

Answer 2

I think the problem may be the substitution variables. In SQL*Plus we need to treat date input as strings. So you need to quote the variables, like this:

to_date(to_char('&C_FROM_DATE','DD-MON-YY') ,'DD-MON-RR') 

Which of course means you don't need the to_char(), so just this:

to_date('&C_FROM_DATE','DD-MON-RR') 

---

Incidentally, at the time of writing it is 2017 so you really really really shouldn't be using the RR mask. Wean yourself off two-digit years otherwise you will inevitably introduce corrupt data into your tables. Use:

to_date('&C_FROM_DATE','DD-MON-YYYY') 

Oracle will still allow you to enter two-digit years (the century defaults to the current one) but at least the intention is clear.

Answer 3

Oracle says : TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype . Anyway as @APC said , you don't need to_char(...) function . If you write something likethis TO_DATE(TO_CHAR(sysdate, 'MM/DD/YYYY'), 'MM/DD/YYYY') is correct , and you don't need additional pharentesis in your code , an example here : select TO_DATE(TO_CHAR(sysdate, 'MM/DD/YYYY'), 'MM/DD/YYYY') from dual; .I want suggest to you,to escape to becoming crazy when you get kinds of errors like right pharentesis missing ,try to check the query and expressions you are writing many times ,executing the query while you are writing , not just at the end.