Could you please help to retrieve Ref Designator from Agile DB?

select Itemid,parentitem.item_number ParentItemNumber, BOMID,bom.item_number BOMITEMNUMBER,
bom.find_number,bom.Quantity,item1.item_number,,, bomitempagetwo.List06,
listentry.entryvalue UOM from
AGILE.item parentitem left join bom on
left join AGILE.Item item1 on bom.item_number=item1.item_number
left join AGILE.PAGE_two bomitempagetwo on
left join AGILE.listentry listentry on bomitempagetwo.list06=listentry.entryid
left join AGILE.refdesig refdesig on
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 which is BOM’s item id.
Is there any other table which has refdes info? Please help.

Add Comment
2 Answer(s)

Hi Manju,

The value is kept inside AGILE.REFDESIG table.  You can join BOM Table with REFDESIG Table as  BOM.ID = REFDESIG.BOM(+)  and then print  REFDESIG.LABEL table which is your reference designator value.


Agile Angel Answered on October 12, 2018.

Hello Arif,

Thanks for your prompt help. I appreciate.
Tried and I get the below error now.


ORA-25156: old style outer join (+) cannot be used with ANSI joins
25156. 00000 – “old style outer join (+) cannot be used with ANSI joins”
*Cause: When a query block uses ANSI style joins, the old notation
for specifying outer joins (+) cannot be used.
*Action: Use ANSI style for specifying outer joins also.
Error at Line: 444 Column: 47

on October 12, 2018.

Sorry couldn’t see this earlier. You must define a function as Kevin shared to fetch RefDes.  Even i am using the similar func.


    str            VARCHAR2(20);
    tmp            LONG;
    first        NUMBER;
    list        LONG;
    OPEN csr;
    first := 1;
        FETCH csr INTO str;
        IF csr%NOTFOUND THEN
        END IF;
        IF first = 1 THEN
            tmp := str;
            first := 0;
            tmp := tmp || ‘,’ || str;
        END IF;
    CLOSE csr;
    list := tmp;
    RETURN list;

on October 15, 2018.
Add Comment

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;
    rdes       varchar2(50);
    refdes    clob;
   refdes := null;
   open csrSRD;
          fetch csrSRD into rdes;
          exit when csrSRD%NOTFOUND;

          if refdes is null then
                refdes := rdes;
                refdes := refdes || ‘,’ || rdes;
          end if;
    end loop;
    close csrSRD;

     RETURN refdes;
show err

 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( REFDES
 from bom b, item i
 where b.change_out = 0 and b.item = 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).

Agile Angel Answered on October 12, 2018.

That is a great explanation and thank you so much Kevin for the quick help.
I am not an SQL expert, but yeah, when I read this I remember all that I had done 10 years ago.
And somehow I didn’t observe the format of  RefDesig  table, misunderstood it in a very wrong way.

Right now, I am working on RefDes function +  optimizing the query,(if time permits).

I believe , these are the basic thump rules which I should be following for the entire migration.
. Thank you for your help, greatly appreciated.

on October 14, 2018.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.