ORA-00907: missing right parenthesis. I am passing a Variable ‘&Coordinator_Id&’

ORA-00907: missing right parenthesis

I am trying to run the code and I am keeping getting the right parenthesis error I checked the entire code. Could somebody see why I am failing?

select * from CUSTOMER_ORDER_JOIN
 where 
CASE  WHEN CF$_ZCOORDINATORID is not null  THEN 
  ((TRIM(upper('&Coordinator_ID&')) LIKE upper((select CUSTOMER_ORDER_API.GET_AUTHORIZE_CODE(ORDER_NO) from dual) ))
    and (OBJSTATE = (select CUSTOMER_ORDER_LINE_API.FINITE_STATE_ENCODE__('Partially Delivered') from dual)
    or OBJSTATE = (select CUSTOMER_ORDER_LINE_API.FINITE_STATE_ENCODE__('Picked') from dual)
    or OBJSTATE = (select CUSTOMER_ORDER_LINE_API.FINITE_STATE_ENCODE__('Released') from dual)
    or OBJSTATE = (select CUSTOMER_ORDER_LINE_API.FINITE_STATE_ENCODE__('Reserved') from dual)))  
    
  ELSE ((TRIM(upper('&Coordinator_ID&')) LIKE upper((select CUSTOMER_ORDER_API.GET_AUTHORIZE_CODE(ORDER_NO) from dual) ) or
         TRIM(upper('&Coordinator_ID&')) is not null)
          and (OBJSTATE = (select CUSTOMER_ORDER_LINE_API.FINITE_STATE_ENCODE__('Partially Delivered') from dual)
          or OBJSTATE = (select CUSTOMER_ORDER_LINE_API.FINITE_STATE_ENCODE__('Picked') from dual)
          or OBJSTATE = (select CUSTOMER_ORDER_LINE_API.FINITE_STATE_ENCODE__('Released') from dual)
          or OBJSTATE = (select CUSTOMER_ORDER_LINE_API.FINITE_STATE_ENCODE__('Reserved') from dual))) 
  END

Answer

As mentioned in comments a case expression evaluates to a value, which you can then use or compare to something else. In a where clause it’s usually simpler to just use Boolean logic, so you can remove the case expression; and you can also remove a lot of the repetition and unnecessary subqueries against dual, to get something like:

select * from CUSTOMER_ORDER_JOIN
where 
(
  TRIM(upper('&Coordinator_ID&')) LIKE upper(CUSTOMER_ORDER_API.GET_AUTHORIZE_CODE(ORDER_NO))
  or (CF$_ZCOORDINATORID is null and TRIM(upper('&Coordinator_ID&')) is not null)
)
and
(
  OBJSTATE = CUSTOMER_ORDER_LINE_API.FINITE_STATE_ENCODE__('Partially Delivered')
  or OBJSTATE = CUSTOMER_ORDER_LINE_API.FINITE_STATE_ENCODE__('Picked')
  or OBJSTATE = CUSTOMER_ORDER_LINE_API.FINITE_STATE_ENCODE__('Released')
  or OBJSTATE = CUSTOMER_ORDER_LINE_API.FINITE_STATE_ENCODE__('Reserved')
) 

Though the LIKE should probably be =, and the upper() in the not null check isn’t really doing anything – can’t tell if the others are necessary; and you could use an in() for the OBJSTATE checks.

I believe that capture the same logic as the version in your question.