Warm tip: This article is reproduced from serverfault.com, please click

Multi values search from one field PLSQL

发布于 2020-12-01 07:42:28

I'm creating a report in PLSQL.

The report is working fine and additionally, I need to add when users search multiple values from one field separating by ;. It should select all the data that he entered.

Example: Salesman - Amanda; Michelle; Sharmain

Then it should select data regarding amenda, michelle and sharmain.

My code only returns data for one value only.

please refer this line from the code and ifsapp.Customer_Order_API.Get_Salesman_Code(i.order_no) LIKE '&Salesman'

where  i.catalog_group='FPMB'
       and i.order_no like 'M%'
       and ((i.invoice_date between to_date( '&From_Date', 'YYYY/MM/DD' ) and to_date( '&To_Date', 'YYYY/MM/DD' ) ) or ('&From_Date' is null and '&To_Date' is null))
       and t.source_ref1=i.order_no
       and (t.source_ref3=i.release_no)
       and (t.source_ref2=i.line_no)
       and i.contract=t.contract
       and t.transaction_code='OESHIP'
       and t.qty_reversed=0
       and t.source_ref1=i.order_no
       and t.serial_no <> '*'
       and t.cost<>0.00
       and i.order_no LIKE '&Order_No
       and ifsapp.Customer_Order_API.Get_Salesman_Code(i.order_no) LIKE '&Salesman'
Questioner
smc develpments
Viewed
0
Popeye 2020-12-01 16:09:22

You can use hierarchy query as follows:

ifsapp.Customer_Order_API.Get_Salesman_Code(i.order_no) IN
(SELECT TRIM(REGEXP_SUBSTR('&Salesman','[^;]+',1,LEVEL))
  FROM DUAL CONNECT BY TRIM(REGEXP_SUBSTR('&Salesman','[^;]+',1,LEVEL)) IS NOT NULL)