Incorrect values in IFS

Hi @Ed22 

It seems like the issue you're facing is related to how you're joining the MAN_SUPP_INVOICE and GEN_LED_VOUCHER_ROW_QRY tables, particularly with the OBJKEY filter. 

Please check the below areas.

1. Join Conditions: You're currently joining the tables on the COMPANY column, but that might not be enough to uniquely match the rows you're looking for. You might need to add another condition, like INVOICE_ID, VOUCHER_NO, or some other shared key, to make sure you're linking the correct rows.

2.  OBJKEY Filter: If OBJKEY exists in both tables, make sure you're using it correctly. You could update the query to include OBJKEY in the JOIN condition. For example:

   SELECT a.internal_invoice_noFROM MAN_SUPP_INVOICE aINNER JOIN GEN_LED_VOUCHER_ROW_QRY b ON a.COMPANY = b.COMPANY AND a.OBJKEY = b.OBJKEYWHERE a.OBJKEY = :objkey;

3. Avoid Duplicates: If COMPANY isn't enough to uniquely identify a row, you could end up with duplicates or unrelated rows. Adding more specific conditions, like INVOICE_NO, VOUCHER_NO, or another column that's common between the tables, can help avoid that.

4. Alternate Query : If OBJKEY is only in MAN_SUPP_INVOICE, but you have another key that links the tables, you can use that key in the JOIN:

SELECT a.internal_invoice_no
FROM MAN_SUPP_INVOICE a
INNER JOIN GEN_LED_VOUCHER_ROW_QRY b 
ON a.COMPANY = b.COMPANY
AND a.some_other_key = b.some_other_key  -- replace 'some_other_key' with a real common column
WHERE a.OBJKEY = :objkey;
 

Make sure you're joining on all the right columns and filtering on the correct keys to avoid duplicates and get the correct data you want.

I'm happy to share my advice on this. If you found the answer helpful, please mark it as the correct answer. This will allow us to close the thread and make it easier for other community users to find and benefit from in the future.

Regards,

Chanuka

“SELECT a.internal_invoice_no FROM MAN_SUPP_INVOICE a
INNER JOIN GEN_LED_VOUCHER_ROW_QRY b 
ON b.COMPANY = a.COMPANY WHERE a.internal_invoice_no = SOMEVALUE“

in SQL developer it retrieves one value in many rows.

Hi @Ed22 
The COMPANY column alone is not enough to uniquely identify the correct row in GEN_LED_VOUCHER_ROW_QRY. If there are several rows in GEN_LED_VOUCHER_ROW_QRY with the same COMPANY value, they will all match to the same record in MAN_SUPP_INVOICE, causing duplicate rows in the result.

To avoid multiple matches, you may need to join on additional columns (like OBJKEY, INVOICE_ID, VOUCHER_NO, or other keys) that uniquely identify rows. 

SELECT a.internal_invoice_no
FROM MAN_SUPP_INVOICE a
INNER JOIN GEN_LED_VOUCHER_ROW_QRY b 
ON a.COMPANY = b.COMPANY 
AND a.OBJKEY = b.OBJKEY  -- or use another relevant column to uniquely join the tables
WHERE a.internal_invoice_no = 'SOMEVALUE';
 

If you are sure that you only need unique internal_invoice_no values and don't care about other columns, you can use DISTINCT to eliminate duplicate rows.

SELECT DISTINCT a.internal_invoice_no
FROM MAN_SUPP_INVOICE a
INNER JOIN GEN_LED_VOUCHER_ROW_QRY b 
ON b.COMPANY = a.COMPANY
WHERE a.internal_invoice_no = 'SOMEVALUE';

I believe the issue arises because the COMPANY column alone is not sufficient to uniquely match the rows in both tables. Adding more specific join conditions or using DISTINCT would help solve your issue.

Regards,

Chanuka

and In entity configuration, in adding new entity, what should I add on field “Arguments”?

this command in Oracle SQL developer works, but in IFS cloud retrieves one internal_invoice_no for all rows. I also tried DISTINCT and joining ON objkey, which is common column, but without change

it is possible to add internal_invoice_no to the entity GenLedVoucherRow to the IFS cloud? For entity GenLedVoucherRow in Additional Views i cannot find column “internal_invoice_no”.