Could you please help to retrieve Ref Designator from Agile DB?
select parentitem.id Itemid,parentitem.item_number ParentItemNumber,bom.id BOMID,bom.item_number BOMITEMNUMBER,
bom.find_number,bom.Quantity,item1.item_number, bomitempagetwo.id, item1.id, bomitempagetwo.List06,
listentry.entryvalue UOM from
AGILE.item parentitem left join AGILE.bom bom on parentitem.id=bom.item
left join AGILE.Item item1 on bom.item_number=item1.item_number
left join AGILE.PAGE_two bomitempagetwo on item1.id=bomitempagetwo.id
left join AGILE.listentry listentry on bomitempagetwo.list06=listentry.entryid
left join AGILE.refdesig refdesig on refdesig.BOM=bom.id
where bom.change_out=0 and parentitem.category=’11111’and ((listentry.langid=0 or listentry.langid=null) or(listentry.entryid is null))
order by parentitemnumber
Please see the sql above.
I have written the query to retrieve BOM,(thanks to myplm community) one of the field RefDes is giving some issues.
Since Refdesig table has every revisions of BOM Item, the sql retrieves multiple rows.
Only unique id I can find is refdesig.bom which is BOM’s item id.
Is there any other table which has refdes info? Please help.
And Arif is correct, LABEL is where the ref-des values are stored in REFDESIG, and the join is done as BOM.ID = REFDESIG.BOM(+).
The bigger problem for a single query is that the ref-des values are stored as separate values in the table, and not as a single consolidated string. So you WILL get as many records for a single BOM line as there are ref-des values, if you simply link the 2 tables using the outer join.
The only way around this is to write a function to get the ref-des values and then have your query use it, instead of doing a join to the table. Something along the lines of :
create or replace function GetRD(bomID IN number) RETURN CLOB IS
cursor csrSRD is select label from refdesig where bom = bomID order by label;
refdes := null;
fetch csrSRD into rdes;
exit when csrSRD%NOTFOUND;
if refdes is null then
refdes := rdes;
refdes := refdes || ‘,’ || rdes;
Note that the above function returns a CLOB value, as you have no idea how long the entire list of ref-des values will be. The above will return anything from a null string to a VERY long string of ref-des values.
And the function can be used as follows :
select i.item_number ASSEMBLY, b.item_number COMPONENT, b.find_number, b.quantity, GetRD(b.id) REFDES
from bom b, item i
where b.change_out = 0 and b.item = i.id and i.category = (select entryid from listentry where parentid = NNN and entryvalue = ‘11111’);
Given everything you are trying to do, it might be easier to put all of the data into a table, update the list values and such using SQL, and to then finally just select all records from the table. Joins such are you are doing for LIST06 are far easier/simpler to do as a single query against a destination table than as you are doing. Just about everything in the Agile database (which is NOT documented in any way, shape or form by Oracle) is an ID, that is then linked to another table that contains the actual text value you need. ITEM.CATEGORY is an example, as it is NUMERIC data type, and must be linked to LISTENTRY to get the text value (just like you are doing with LIST06).