Self Join Of BOM Table in AX

Finance and Operations/AX Developer Forum
1

Hi All,

I have the following scenario where i need to join BOM Table to some “X” table which has RefReciD, alternate Item Number joining to RecID of BOM

The Query would be Select X.AlternateItemNumber JOIN BOM where X.RefRecid == BOM.RecID and BOM.ItemNumber = ‘ABC’

Once i get all the alternate itemnumbers from Table ‘X’ i need to check to see if there are any items to BOM Table The query should be

Select * from BOM outer Join X where X.ALtItemID == BOM.itemID

How do i achieve this one Query … Thanks in advance.

2

It looks like you’re going about it a tad backwards if your table relations are valid. I’d switch the BOM.RecId == X.RefRecId with BOM.ItemId == X.AltItemId maybe. I’d try something like:

while select BOM.ItemId

outer join X

where BOM.RecId == X.RefRecId

{

info(BOM.itemId);

}

3

Thanks Alex. Can we join the same BOM table twice one with a JOIN and other with a Outer JOIN for retrieving the BOM details of table X Alternate Item ID from BOM. ?