Error during retrieving SQL command

The part_description in PART_SERIAL_ISSUE IS the part description in PART_CATALOG, and is what you’re getting when you’re utilizing part_catalog_api.get_description

For every Part Serial you have in the Part Serial Catalog, are you trying to retrieve the description of the PART, or the description of the SUPERIOR Part ?

From your screenshot, the superior Part is 3106----, its description is 1510W---, and that’s what you’re trying to retrieve for any Part Serial that has 3106--- has its superior part no ?

In which case, like I said, you must use superior_part_no as a bind variable but you can’t do that cause it is not an approved persistent field in part_serial_issue.

You can get around this using Objkey as I explained.

argument: objkey

select statement:

select p.description from

part_serial_catalog t

left join part_catalog p on p.part_no = t.superior_part_no

where t.objkey = :objkey

This will retrieve the description from the superior_part_no (if one exists) in your custom attribute.

Thank you Simon for your time and advices, by the way, If there is no views in “Additional Views”, I cant add there nothing? Currently I want to add column in entity RoutingAlternate, but “Additional Views” is there empty. 

All entities that you can add custom attributes to have at least one base view.

They may not have ADDITIONAL views, but they always have a base view :)

Typically the base view would be the name of the entity with underscores between words, so here, the base view is ROUTING_ALTERNATE

You can use any of the persistent fields in there as bind variables, if you want to add a custom attributes to that view.

how can I see this table which you post in picture? I see only with values through SELECT * FROM ROUTING_ALTERNATE

This screenshot is from PL/SQL Developper, connecting to the database and investigating the packages etc.

You can do that on the Build Place if you’re Cloud Hosted by IFS (IFS typically don’t give direct PL/SQL Access on the Use Place environments), or if you’re doing your own hosting, your IT team should be able to access the DB and investigate.

Whichever user you use to connect to the DB, if not IFSAPP, will need the underlying privileges to access views/packages as they all belong to IFSAPP.

You would absolutely need to use IFSAPP in order to do a direct SQL Query on the _tab tables, as these are typically never granted to end users (nor can they even BE granted through the Frontend, it’d have to be a backend Oracle grant which is not advised), and end users only get access to the views that use these tables, not the tables themselves.

select p.description from

part_serial_catalog t

left join part_catalog p on p.part_no = t.superior_part_no

where t.objkey = :objkey

When i am not sure what two columns have two tables common, Can I join two tables on objkey value? 

Objkey is unique but per entity.

So for example, views PART_SERIAL_CATALOG and PART_SERIAL_ISSUE are both based on the part_serial_catalog_tab, and so they share the same Objkey (which is the rowkey of the base table).

The Objkey in RoutingAlternate for instance would not be the same Objkey as the PartSerialCatalog as these are different Logical Units/Entities, so you couldn’t join both tables/views using Objkey.

In the other example, I want to connect two views on their common column, which is COMPANY, like this: “SELECT a.internal_invoice_no
FROM MAN_SUPP_INVOICE a
INNER JOIN GEN_LED_VOUCHER_ROW_QRY b 
ON a.COMPANY = b.COMPANY 
WHERE a.OBJKEY = :objkey” and filter resuts by objkey, I want to display in entity “GenLedVoucherRow” column “Internal Invoice No” which is in view MAN_SUPP_INVOICE, but it retrieves null values. How can I repair this query? Is possible connect this two views? 

Gen led Voucher Row and Man_Supp_Invoice don’t share a rowkey as they’re not based on the same base table, so it’s not possible to join them based on objkey.

You can just do a select on both same and see what fields they have in common for the join.

Then you’d probably need to join on company and invoice ID if both are available.

yes, they have company common but I dont know what should add in where, because objkey returns null values and internal invoice no (which I want to retrieve) in entity “GenLedVoucherRow” is only in Man_Supp_Invoice view

I don’t currently have access to a cloud environment so I can’t help you there.

But again just do select  * from gen_led_voucher_row_qry and see what’s available to join on that is also present in man_supp_invoice.

What you really want is invoice id or invoice_no, and company_id

Hello, could you please give me advice, I want to retrieve note_text from Shipment view, but it always will fetch empty string in IFS Report Designer. I would add there filtering by shipment id, but this view is not allowed in additional views, IN additional views are only allowed views which are connected with Customer Order tab, so only CUSTOMER_ORDER

This is my SQL query in CustomerOrder entity:

SELECT s.note_text FROM Shipment s
JOIN CUSTOMER_ORDER c 
ON c.COMPANY= s.COMPANY
WHERE c.order_no = :order_no

arguments: order_no

I have also problem with this types of queries where I want to filter by some column which not belongs to any table in additional views.