<< Click to Display Table of Contents >> Navigation:  The BOM > Modifying the SQL BOM

Modifying the SQL Bill Of Materials

The SQL query that build the BOMs are available for editing, but not in the edit box of the query page.This limitation is due to the presence of variable fields.

Variable fields are expressed in the SQL query as %s and indicate the ID of the parent document. Do not modify the lines containing %s as they are necessary and correct as they are; you can anyway add your own fields and conditions where necessary.

To edit this query, please use any text editor (i.e. Notepad) and edit the file /SQL/BOM.sql directly.

MechworksPDM calls sql/Bom.sql for level 1 BOMs and sql/Bomhier.sql for higher level BOMs.

The original BOM query

SELECT

 CHILD_INDEX AS POS,
 DOCUMENT.T AS T,
 DOCUMENT.ID AS ID,
 DOCUMENT.DESCRIPTION AS DESCRIPTION,
 %s.CNT
FROM
 %s,
 DOCUMENT
WHERE
 %s.UNIQUE_ID=DOCUMENT.UNIQUE_ID AND
 DOCUMENT.T In ('P','D','A') AND
 DOCUMENT.MAKE_BUY NOT In ('NO_BOM')
ORDER BY
 %s.CHILD_INDEX

Adding a field

SELECT

 CHILD_INDEX AS POS,
 DOCUMENT.T AS T,
 DOCUMENT.ID AS ID,
 DOCUMENT.DESCRIPTION AS DESCRIPTION,
 DOCUMENT.CONFIGURATION AS CONFIGURATION,
 %s.CNT
FROM
 %s,
 DOCUMENT
WHERE
 %s.UNIQUE_ID=DOCUMENT.UNIQUE_ID AND
 DOCUMENT.T In ('P','D','A') AND
 DOCUMENT.MAKE_BUY NOT In ('NO_BOM')
ORDER BY
 %s.CHILD_INDEX

Being that the BOM query retrieves field data from more than one table, you need to indicate the field in the format TABLE_NAME.FIELD_NAME. You can refer to SCHEMA.SQL  for a complete list of the standard fields.

The AS clause indicates the name displayed as the column header. Setting CONFIGURATION we avoid displaying DOCUMENT.CONFIGURATION   as a column header: it would be too long and would not convey any better information when you look at the resulting table.

Adding a condition

The conditions are added after the WHERE clause:

SELECT

 CHILD_INDEX AS POS,
 DOCUMENT.T AS T,
 DOCUMENT.ID AS ID,
 DOCUMENT.DESCRIPTION AS DESCRIPTION,
 %s.CNT
FROM
  %s,
  DOCUMENT
WHERE
 %s.UNIQUE_ID=DOCUMENT.UNIQUE_ID AND
 DOCUMENT.T In ('P','D','A') AND
 DOCUMENT.MAKE_BUY NOT In ('NO_BOM') AND
 DOCUMENT.COST > 0
ORDER BY
 %s.CHILD_INDEX

To insert a new condition, you need to link it to the last pre-existing one with an AND or OR depending on the result you want to obtain. In this case we discard all the elements for which the cost has not been defined and is hence 0.

Changing the list order is straightforward: replace the field following the ORDER BY clause with the field you want to use as a reference for sorting.