Error during retrieving SQL command

Hi

If you are querying part serials then the join must include all key fields not just the part number.

I believe you need to include the serial number as part of the join.

Hope this helps.

Concur with @MMcK.

What sort of “not working” do you mean, are you getting an error message? Or just empty data?

It would help if you actually told us the views you’re querying rather than view1 and view2 :)

this is my query (1) and in (2) is output, but I want display in “Nazov” column Superior Part No and “Popis polo” column. this would be correct: “322528_SRV 309529 - PBA ELA” not this: “309529 - PBA ELA”

Hi Ed22

I’m not sure what you mean by ‘Nazov’ but if you are expecting a row for each entry in the part_serial_catalog to pick up some information from the part_catalog then your query is the wrong way round?

select a.superior_part_no || ‘ - ‘ || b.Description

from part_serial_catalog a

join part_catalog b on a.part_no = b.part_no

Is that what you need?

You’re joining part_serial_catalog on itself (as you’re  doing a custom attribute on PartSerialCatalog), this is very inefficient, why are you doing this?

Can you explain what you’re trying to get exactly, using english terms for the column names ?

From what I’m trying to decipher, you’re trying for each part serial to retrieve the superior part number concatenated to the description of the part itself (not the description of the superior part no?)

You should use superior_part_no as an argument, then do it like this:

arguments: part_no, superior_part_no

If you want to retrieve the description of the MAIN part:

select :superior_part_no || ' - ' || PART_CATALOG_API.Get_Description(:PART_NO) from dual

or more simply, add description as an argument, and do it like this:

select :superior_part_no || ' - ' || :description from dual

If, more likely, what you’re trying to retrieve the is the description of the SUPERIOR Part no:

select :superior_part_no || ' - ' || PART_CATALOG_API.Get_Description(:SUPERIOR_PART_NO) from dual

This would error out as soon as you have more than one serial for a given part no in the part serial catalog as the select statement would return multiple rows :)

ORA-01427: single-row subquery returns more than one row

Hi SimonTestard

The query above doesn’t error because there is only one part_catalog entry for each serialised part in the part_serial_catalog.  Yes, there are multiple part serials, but the join is correct and doesn’t error because of the single part_catalog part_no.

There is no subquery, it’s a direct join, so it wouldn’t show a subquery error.

I want display column “Together” in IFS cloud, in IFS this column is called “Nazov”. 

My bad, I mis-explained this, but:

1. The code you’ve provided absolutely would return multiple rows (just run it yourself for a given part number and you’ll see).
    

   

   
   
   
   You’re joining Part Catalog from Part Serial Catalog, unless you’re filtering on both Part_No and Part_Serial, you will absolutely get multiple rows (this is why I concurred with your first post saying the Part Serial should be used).

2. You can’t create a custom read only field with no arguments, and your code does not provide any bind variable.
    

   select a.superior_part_no || ‘ - ‘ || b.Description

   from part_serial_catalog a

   join part_catalog b on a.part_no = b.part_no
   
   You have no bind variable in there so you couldn’t create a custom read only field only using this code above.

If you add a bind variable here like “WHERE p.part_no = :part_no”, the custom field, called cf$_nazov, would be called as part of Part_Serial_Catalog_CFV as Part_Serial_Catalog_CFP.get_cf$_Nazov

The method itself is done as a straight cursor

It won’t error per se but the get method actually does a simple fetch meaning it will show the first result it find for that part no in the part serial catalog.

So, in this case, if there are Part Serials that have the same part number, but a different Superior Part No, the cursor would return the first match it finds, and return potentially the wrong superior part number.

It won’t error with as I think IFS did a simple fetch on purpose to avoid no_data_found and multiple rows in subqery errors, but you have no assurance of any data integrity there.

So you want to display the Superior Part No of the Serial, concatenated with the Description of the Part Number for the Serial ?

Are you SURE you don’t want to display the SUPERIOR Part No Description ? (The Superior Part No Shown will not be a match to the Description shown).

If you are SURE, what I said above will work:

select :superior_part_no || ' ' || PART_CATALOG_API.get_description(:part_no) from dual

You just need to enter superior_part_no and part_no as arguments.

If you want to avoid an empty space if superior part_no is null, you can do a case statement too such as

select (case when :superior_part_no is not null then :superior_part_no || ' ' || PART_CATALOG_API.get_description(:part_no) else PART_CATALOG_API.get_description(:part_no) end)  from dual

Hello,

In “Add Custom Attribute” should I add in “Custom Field Impl Type” Select Statement or Expression? Because I think that this “PART_CATALOG_API.get_description(:part_no)” is expression and in SELECT it is not working

You should use Select Statement.

It works just fine, you can of course use Package Methods (as long as they’re functions, not procedures), in any select statement, including the select statement of a read only custom field.

I have this error: 

So, classical joins are not allowed in this field Select Statement, instead of this is allowed name of view + parameter, like in this case: part_serial_catalog_api.get_description? There is always need after the name of view “_api” add + this one“get_NAME_OF_PARAMETER”?

No, you can absolutely do classical joins, but it will create a cursor and fetch the first entry it finds (which you need to be careful of), but the arguments MUST always be present on any of the approved view.

For this reason, if you’re going to use joins, it’s always a good idea to filter the query by Objkey as this will always be unique. In this case, the code I gave you above could easily be replaced by this:

Argument: Objkey

Select statement:

Select

(case when p.superior_part_no is not null then p.superior_part_no || ' ' || c.description else c.description end) as nazov

from PART_SERIAL_CATALOG p

left join PART_CATALOG c on c.part_no = p.part_no

WHERE p.objkey = :objkey

This would work exactly the same and give the same result, but by filtering on objkey, you ensure that the cursor will only have 1 row that corresponds exactly to the part serial you’re looking for (and so avoid data integrity issue if you have different serials of the same Part No, but that have different superior part Nos).

Objkey as an argument will always be present as it is a prerequisite for any approved view to have Objkey as a field.

Whatever code you create gets converted by IFS as a Method Function, that function will run your select statement as a cursor and return the first result it fetches into the RETURN value of said function.

The views that display that custom field then simply execute that method call when that field is part of any select statement.

and, for example, when I want to write SELECT Statement in IFS cloud: “SELECT b.description FROM PART_SERIAL_CATALOG a
JOIN PART_CATALOG b
ON a.part_no= b.part_no
WHERE b.description = '14d5sad' AND a.part_no= '11adsad919'” what should I write to the field “Select Statement” and “Arguments”? 

I have this error, is there error in field Additional Views?

You must always use at least one argument/bind variable when utilizing Select Statement Custom Read Only Attributes

You can’t use Description as a bind variable/argument because description does not exist as a field in view PART_SERIAL_CATALOG, so that’s why in my earlier message I used an API Call to retrieve it :)

You can only use fields for arguments that exist in all the views approved for the custom attribute (you can see which views by clicking the top button)

PART_SERIAL_CATALOG does not natively retrieve the part description so you can’t use that as a bind variable, nor can you retrieve it in your select statement from that view

The attributes used as bind variables also must be persistent attributes in the entity the view(s) are created from

That code you’re doing doesn’t make much sense though, so I’m not sure how I can help you further here.

And it is possible use in entity PartSerialCatalog some field from different view, for example Description from InventoryPart? In this page, which is Overview - stock items i Have item description and I want to use this description in entity PartSerialCatalog, specifically in column Name

Again you won’t be able to do the first screenshot because description is not a persistent attribute within the PartSerialCatalog entity, when you see the part description on serials it comes from PartCatalog, it’s not a persistent field you can use as a bind variable.

If you want to grab the description from Inventory Part window, you’d need to grab it using other bind variables, but InventoryPart is site specific and you don’t have Site on PartSerialCatalog, so you CAN grab it but you have no idea whether the cursor will grab the description from the right site.

In entity PartSerialCatalog is these Additional views: and this is mySQL command: But unfortunately, I got error. I want to retrieve part_description column and this belongs to PART_SERIAL_ISSUE view which is part of entity PartSerialCatalog

part_description is not a persistent attribute of PART_SERIAL_ISSUE.

As you can see, it is retrieved through an API call, because the part description sits in the PartCatalog, other entities just reference it.

I don’t really understand what you’re trying to achieve, if you want a custom field to retrieve description, you just go “select part_catalog_api.get_description(:part_no) from dual”, that’s all, not sure why you’re trying to use description as a bind variable in any way whatsoever.

I know it may sound confusing, but I want to retrieve description of the item, although you command is working, I want another description. This description is in view PART_CATALOG or part_description in PART_SERIAL_ISSUE. I tried to add SQL: “SELECT description FROM PART_CATALOG 
WHERE PART_NO= :part_no” with argument part_no, in Oracle it retrieves correct values but in IFS dont. 

Everytime when I call part_description or description, it retrieves “HEATING FOIL, Hybride control, etc...” in column “Name” ike in first pic, but in column “Name” I want for every superior Part No = 3160 in “Name” column value “1510W” which is in picture 2. 

also the same value is in entity PartCatalog.