SQL statement returns no value due to value separated by semicolon

BZNSIMON wrote:

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 @BZNSIMON ,

Supplier invoice can be created for number of PO references. That is the reason for having several PO numbers in PO_REF_NUMBER column in this window for one invoice. If you need to get  the authorize code of the connected PO then what is  your expectation here if there are number of POs ? Do you expect your custom field to view all the authorize_code of all the PO s connected. If that is the requirement then I doubt whether that can be achieved by a simple SELECT statement.

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 @Dumeesha Wanigarathna 

My desired outcome is that IFS shows multiple Coordinator Names in the custom field, separated with a semicolon for example. 

BZNSIMON wrote:

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; 

BZNSIMON wrote:

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)

Hi @EntNadeeL , 

I managed to publish the custom field with the statement you provided, but I did not get the desired result. The field displays the Coordinator Name when there is one Purchase Order as reference, but it displays nothing when there are multiple Purchase Orders as reference. Please see the screenshot below. Do you know what is wrong? 

Hi @EntNadeeL 

I also changed the comma is the last line to a semicolon and then I got the desired result! I aslo changed it in the first line, because now the results are separated with a semicolon instead of a comma. 

Many thanks for your help!! 

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.