This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

I've gotten a single statement to return a fully exploded bill of materials using connect by prior! Now I need to calculate the Quantity per Top Level Assembly and am stuck.  The Idea is that if Assembly A has three layers of sub-assemblies and each sub assembly require 2x per build then at the lowest level (sub assembly 3) I would need 8x of each component. It looks like CONNECT_BY_ROOT and SYS_CONNECT_BY_PATH do not work even though CONNECT BY PRIOR, LEVEL and CONNECT_BY_ISLEAF work.  Anyone have any clever ideas?

```SELECT 
    REPLACE(RPAD('X', level, 'X'), 'X', UNISTR('\2007')) || BUILTIN.DF(item.id) AS indented_column,
    itemAssemblyItemBom.assembly AS bomAssembly,
    bom.id AS bomID,
    bomRevision.id AS bomRevID,
    BUILTIN.DF(bomRevisionComponent.item) AS component,
    bomRevisionComponent.item AS componentid,
    LEVEL as level,
    item.itemType AS itemtype,
    bomRevisionComponent.itemSource as itemsource,
    bomRevisionComponent.quantity as quantity,
    bomRevisionComponent.bomQuantity as bomquantity,
    componentItem.itemType AS componentItemType
FROM 
    item
JOIN 
    itemAssemblyItemBom ON item.id = itemAssemblyItemBom.assembly
JOIN 
    bom ON itemAssemblyItemBom.billofmaterials = bom.id
LEFT OUTER JOIN 
    bomRevision ON itemAssemblyItemBom.currentrevision = bomRevision.id
LEFT OUTER JOIN 
    bomRevisionComponent ON bomRevisionComponent.bomrevision = bomRevision.id
LEFT OUTER JOIN 
    item componentItem ON bomRevisionComponent.item = componentItem.id
CONNECT BY PRIOR bomRevisionComponent.item = itemAssemblyItemBom.assembly
START WITH item.id = 8041;```

Also pretty frustrating that SQL-92 and Oracle SQL (what version) are supposed to be supported but it seems super unreliable and documentation is trash.

Unfortunately, I haven't been able to get NetSuite to do the recursive math that would be required for these calculations, traversing through all levels of the BOM.  I've had to do scripts for this same use case

It's a real shame, your new NimbusDash would be a wonderful place to leverage this (since you already got the indentation working for me!) Thanks for the response. I am considering a script to do the just the math part of this and then use the SQL to pull the data to keep reporting flexible.