奥拉-00907:缺少右括号。我正在传递一个变量'&Coordinator_Id&‘

Question

ORA-00907:缺少右括号

我正在尝试运行代码，并且一直得到正确的括号错误，我检查了整个代码。有人能明白我为什么失败吗？

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 1

正如注释中提到的那样，案例表达式的计算结果是一个值，然后您可以使用该值或将其与其他值进行比较。在where子句中，只使用布尔逻辑通常更简单，因此可以删除case表达式；还可以删除大量针对dual的重复和不必要的子查询，以获得以下内容：

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')
) 

尽管LIKE可能应该是=，而not null检查中的upper()并没有真正做任何事情--但是不能判断其他的是否必要；您可以使用in()来进行OBJSTATE检查。

我相信这与你问题中的说法是一样的。