Help properly joining to the BOM table
Hi There –
I have recently succeeded in linking an excel spreadsheet to our Agile database. I am quite happy with this because I can immediately see live data pursuant to whatever I am querying, but the data is in excel. I am writing my SQL query in the Oracle SQL develop application and just doing a copy/paste into the proper excel area.
My question is in using the Oracle SQL developer, I am having trouble writing a query which properly converts some of the references to their actual value. What I mean by this is that for example, the LIST01 column on the BOM table will be some random digits (123456789), and these correspond to anther table, probably the NODETABLE table via the ENTRYID column, where the actual value would be the ENTRYVALUE.
In joining these, I get no results, and I know there should be some….so I am probably not joining the correct tables/columns.
Any help would be appreciated. I am mainly looking for information on what tables are releated to the BOM table.
Nick
You need to be joining to the listentry table on entryid and retrieving entryvalue to get the text you require.
Hi Adrian – Thanks for the guidance. Unfortunately, I have tried this combo as well and get no results. 🙁
SQL clip is…
INNER JOIN AGILE.LISTENTRY
ON AGILE.BOM.LIST01 = AGILE.LISTENTRY.ENTRYID
Nick
Right idea, wrong table. If you are running Agile 9.x, list values will be in the LISTENTRY table, using ENTRYID to get the ENTRYVALUE value.
Something along the lines of :
select b.find_number, b.item_number, b.quantity, l.entryvalue “LIST1” from bom b, listentry l where b.list01 = l.entryid(+);
Note the outer join, it is needed even if you *know* there is always a value in LIST01. Because sometimes, there won’t be a value.
To be 100% correct, if the configurations for the Part BOM tab and Document BOM tab are different (list01 is used on one and not the other, or even worse, it is used in both but with different lists), you should include the list parent ID in the query :
select b.find_number, b.item_number, b.quantity, l.entryvalue “LIST1” from bom b, listentry l where l.parentid=2471234 and b.list01 = l.entryid(+);
And then have separate queries in Excel with the correct parent ID, depending on whether you are viewing a Part or Document BOM.
If you have more than one list attribute in use, you must do a separate join on each one with LISTENTRY to correctly get the list values.
This works for me….
select LE.ENTRYVALUE from bom B
inner join listentry LE ON B.LIST01 = LE.ENTRYID
where list01 is not null;
Post your full SQL
I think LIST01 has values since there is something there, but don’t know what it is yet. After determined, I’ll set an alias. Need to do for LIST02, and the Change_out/in dates. In this query, the entryvalues display as null.
SELECT AGILE.ITEM.ITEM_NUMBER AS “PARENT ID”,
AGILE.ITEM.DESCRIPTION AS “PARENT NAME”,
AGILE.BOM.ITEM_NUMBER AS “CHILD ID”,
AGILE.BOM.QUANTITY AS QTY,
AGILE.BOM.TEXT04 AS TORQUE,
AGILE.BOM.LIST01,
AGILE.BOM.LIST02,
AGILE.BOM.CHANGE_OUT,
AGILE.BOM.CHANGE_IN,
AGILE.BOM.PRIOR_BOM,
AGILE.BOM.COMPONENT,
AGILE.LISTENTRY.ENTRYVALUE
FROM AGILE.BOM
INNER JOIN AGILE.ITEM
ON AGILE.ITEM.ID = AGILE.BOM.ITEM
LEFT JOIN AGILE.LISTENTRY
ON AGILE.BOM.LIST01 = AGILE.LISTENTRY.ENTRYID
WHERE AGILE.ITEM.ITEM_NUMBER = ’01-08197′
Strangely, I switched over to LIST02 and I am getting results. I think my SQL query is now good, but something weird is going on with LIST01 (as Kevin hinted to).
Anyone have any tips on the change_out and change_in values?
Find a distinct ID value from bom.list01 (select distinct list01 form bom). Then “select * from listentry where entryid = <one of the ID values>”.
Hmmm, look at the configuration for list01 in the BOM tab. Open the attribute, click on “View Detail” for the list and look at the List Type. If it is not Simple, then the above answers will not work, and you will need to use something more complicated. If the list type is Cascade (consists of multiple values) you will need to create a function to correctly get the full list value. If it is Dynamic (it is an object reference), you will need to link to an object table to get the correct “list” value.
Kevin – Sorry for being a novice here. I’ve attempted to follow your instruction and do some googling, but cannot locate how to get to this screen on oracle sql develeoper.
I did run a distinct query…
SELECT DISTINCT LIST01,
AGILE.LISTENTRY.ENTRYVALUE
FROM AGILE.BOM
LEFT JOIN AGILE.LISTENTRY
ON AGILE.BOM.LIST01 = AGILE.LISTENTRY.ENTRYID
I get 17 rows with values for LIST01, but ENTRYVALUE are all null.
If I click on the BOM table, and then view the tab that gets opened, it starts with “Columns”. See screenshot. I cycled through all of those and none seemed to be the correct area to view the column attributes of LIST01.
I am not referring to anything about the database. You need to go into Agile using the Java client and do what I asked. The Agile configuration is mostly contained within NODETABLE and PROPERTYTABLE, but believe me, you do not want to go exploring there unless you know what you are doing. You can do what I asked with the help of your Agile administrator, they will know what I am talking about. I strongly suspect that the list is either dynamic (i.e. objects) or a cascade list, but far less likely that it is a cascade list because the right-most values would show up in that case. Good luck!
change_in is the id of the change in the table change, so you would need another join like
join agile.change C on agile.bom.changes_in = c.id
Then select the fields from the change table you want your query to return.
Repeat for change_out
Try this too, as I understand how confusing it is sometimes to put the BOM table together:
SELECT j.item_number AS parent_item,
find_number,
b.item_number,
i.description AS item_description,
quantity,
f.text AS BOM_notes,
date01 AS phased_in,
date02 AS phased_out
FROM agile.bom b
LEFT OUTER JOIN agile.agile_flex f
ON b.item = f.id
AND f.attid = 1036
AND b.id = f.row_id
JOIN agile.item i
ON b.component = i.id
JOIN agile.item j
ON b.item = j.id
WHERE change_out = ‘0’
AND j.item_number = :parent_item;
