SQL statement returns no value due to value separated by semicolon

Hi, 

I created a Custom Field in the LU IncomingInvoice which returns the Coordinator Name based on the PO Reference. But there could be multiple PO References separated with a semicolon. When that is the case IFS returns no value. Is it possible to solve this issue, so that IFS returns multiple values when there are multiple PO References. Please see the screenshots below. 

Arguments:

v.company, v.po_ref_number

Select Statement: 

SELECT t.cf$_coordinator_name FROM Purchase_Order_cfv t WHERE t.company = :COMPANY AND t.order_no =:PO_REF_NUMBER

 

Kind regards,

Simon

Hi @BZNSIMON ,

You have to break all the PO in REF_NUMBER and then get relevant coordinators and then concatenate them.

There could be simple changes. But this is the way. 

Try below

SELECT LISTAGG(t.cf$_coordinator_name, ',') FROM Purchase_Order_cfv t 
WHERE t.company = :COMPANY AND t.order_no IN 
(SELECT trim(regexp_substr('PO123,PO456', '[^,]+', 1, LEVEL))
FROM dual
CONNECT BY LEVEL <= regexp_count('PO123,PO456', ',')+1)

Regards,

Hi @EntNadeeL 

When I try your suggestion, I receive an Error Message. Please see the screenshot below. This is the data I used for the statement, can you see what is wrong: 

Arguments:

v.company, v.po_ref_number

Select Statement: 

SELECT LISTAGG(t.cf$_coordinator_name, ',') FROM Purchase_Order_cfv t WHERE t.company = :COMPANY AND t.order_no IN (SELECT trim(regexp_substr('PO123,PO456', '[^,]+', 1, LEVEL)) FROM dual CONNECT BY LEVEL <= regexp_count('PO123,PO456', ',')+1)

Hi @EntNadeeL 

 

When I try your suggestion, I receive an Error Message. Please see the screenshot below. This is the data I used for the statement, can you see what is wrong: 

 

Arguments:

v.company, v.po_ref_number

Select Statement: 

SELECT LISTAGG(t.cf$_coordinator_name, ',') FROM Purchase_Order_cfv t WHERE t.company = :COMPANY AND t.order_no IN (SELECT trim(regexp_substr('PO123,PO456', '[^,]+', 1, LEVEL)) FROM dual CONNECT BY LEVEL <= regexp_count('PO123,PO456', ',')+1)

 

 

Hi @EntNadeeL ,

I didnt expect you to put the same code I sent. Because you have to replace the string with po_ref_number parameter like below.

SELECT LISTAGG(t.cf$_coordinator_name, ',') FROM Purchase_Order_cfv t 
WHERE t.company = :COMPANY AND t.order_no IN 
(SELECT trim(regexp_substr(:po_ref_number, '[^,]+', 1, LEVEL))
FROM dual
CONNECT BY LEVEL <= regexp_count(:po_ref_number, ',')+1)

Hi @EntNadeeL 

I am sorry, i misunderstood. I tried it and received the following error message; 

 

 

Ah yes that was my bad.. with the keyword LISTADD there are few other things

SELECT LISTAGG(t.cf$_coordinator_name, ',') WITHIN GROUP (ORDER BY t.cf$_coordinator_name) "Coordinator_list" 
FROM Purchase_Order_cfv t 
WHERE t.company = :COMPANY AND t.order_no IN 
(SELECT trim(regexp_substr(:po_ref_number, '[^,]+', 1, LEVEL))
FROM dual
CONNECT BY LEVEL <= regexp_count(:po_ref_number, ',')+1)

Good to hear that this got successful.