"ORA-00920: invalid relational operator" Error

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

Hi everyone,

when I run this code through Oracle SQL Developer and I add two items to the :list parameter and two items to the :action parameter it works fine. But when I run this through SSRS (report builder ) it fails to run and I get an "ORA-00920: invalid relational operator". I'm new to SQL and i'm not sure what I am doing incorrectly here. Any help is greatly appreciated. Thanks!

Answer 1

There are 2 ways to do this:

1. Multiple Value Parameter: First of all, you must use Oracle provider, multiple value paramaters does not work with neither ODBC nor OLEDB connections (reference). Here is an external link explaining in detail here.

2. Using an expression as the query by putting the whole thing like this ="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"

In this case you will need to provide default empty values to your lists. I used 'N/A' in my example.