"ORA-00920: invalid relational operator"错误

Question

select 
ic.item_name,
lh.locn_brcd from_locn,
lh2.locn_brcd to_locn,
wl.from_container,
wl.to_container,
wl.units,
wl.prev_from_container_status prev_from_lpn_status, 
wl.curr_from_container_status curr_from_lpn_status,
wl.prev_to_container_status prev_to_lpn_status,
wl.curr_to_container_status curr_to_lpn_status,
wl.work_batch_number,
wl.transaction_name,
wl.action,
wl.work_id,
wl.date_updated,
wl.source_updated,
wl.tote_number,
wl.chute     
from m_work_log wl     
LEFT join item_cbo ic on wl.item_id=ic.item_id
left join locn_hdr lh on wl.from_location_id = lh.locn_id
left join locn_hdr lh2 on wl.to_location_id = lh2.locn_id    
where wl.action in (:action)    
and trunc(wl.date_updated) between :start_date and :end_date
and (ic.item_name in (:list) OR  
wl.source_updated = :username OR 
wl.to_container in (:LPNList) OR 
(:list is null and :username is null and :LPNList is null)
)
order by date_updated desc

大家好，

当我通过Oracle SQL开发人员运行此代码时，我将两个项目添加到：list参数，将两个项目添加到：action参数，它工作正常。但是当我通过SSRS(报表生成器(运行它时，它无法运行，并且我得到一个"ORA-00920：无效的关系运算符"。我是SQL的新手，我不确定我在这里做错了什么。任何帮助将不胜感激。谢谢！

Answer 1

有两种方法可以做到这一点：

1. 多值参数： 首先，您必须使用 Oracle 提供程序，多值参数不适用于 ODBC 和 OLEDB 连接(参考(。 这是一个外部链接，在这里详细解释。

2. 使用表达式作为查询，将整个事情放到这样的="query_here"

="select "
+ "    ic.item_name,"
+ "    lh.locn_brcd from_locn,"
+ "    lh2.locn_brcd to_locn,"
+ "    wl.from_container,"
+ "    wl.to_container,"
+ "    wl.units,"
+ "    wl.prev_from_container_status prev_from_lpn_status,"
+ "    wl.curr_from_container_status curr_from_lpn_status,"
+ "    wl.prev_to_container_status prev_to_lpn_status,"
+ "    wl.curr_to_container_status curr_to_lpn_status,"
+ "    wl.work_batch_number,"
+ "    wl.transaction_name,"
+ "    wl.action,"
+ "    wl.work_id,"
+ "    wl.date_updated,"
+ "    wl.source_updated,"
+ "    wl.tote_number,"
+ "    wl.chute"
+ "from m_work_log wl"
+ "    LEFT join item_cbo ic on wl.item_id=ic.item_id"
+ "    left join locn_hdr lh on wl.from_location_id = lh.locn_id"
+ "    left join locn_hdr lh2 on wl.to_location_id = lh2.locn_id"
+ "where wl.action in (:action)"
+ "    and trunc(wl.date_updated) between :start_date and :end_date"
+ "    and (ic.item_name in ('" + Join(Parameters!list.Value , "', '")" + ') OR"
+ "         wl.source_updated = :username OR"
+ "         wl.to_container in ('" + Join(Parameters!LPNList.Value , "', '")" + ') OR"
+ "         (:list = '_N/A_' and :username is null and :LPNList = '_N/A_')"
+ "        )"
+ "    order by date_updated desc"

在这种情况下，您需要为列表提供默认空值。我在示例中使用了">N/A"。