cancel
Showing results for 
Search instead for 
Did you mean: 

How to query all items assosiated with the parent item at once (Bill of material)?

Former Member
5,062

Dear All.

I'm very new to ASA. I would like to develop an application for my company, it's production company so it requires to have BOM. Let's say I have a table which recursive relationship as follow.

bom_master

  • item_id
  • qty
  • parent_item_id

Just like usual table structure found on the internet, for the parent item, it's parent_item_id contains null. My question is that how to query one single parent item with all it's child (no matter how many level will be) at once? Do I need a stored procedure or this can be done using plain SQL statement? Please give me some samples or hits.

Thanks Kongthap

Accepted Solutions (1)

Accepted Solutions (1)

Breck_Carter
Participant

Have a look at Parts explosion problems.

Former Member
0 Kudos

Wow. Save that one for your next book. 🙂

Breck_Carter
Participant
0 Kudos

Hah, hah... there is an example in my last, um, only book. This question is making me think I'll self-plagiarize republish it in my blog 🙂

VolkerBarth
Contributor
0 Kudos

FWIW: The cited solution requires recursive common table expressions (CTEs) and as such, requires v9 and above - so for older versions, I guess a stored procedure will be necessary.

VolkerBarth
Contributor

Breck has republished the according part of his book in his blog (and I feel free to publish the link:))

Example: RECURSIVE UNION

Answers (1)

Answers (1)

Former Member
0 Kudos

Following the question: How fast is "RECURSIVE UNION"? Each level is permanent 'select' and in case of distributed databases with multiple tables ? Any Evaluation Tests...?!

VolkerBarth
Contributor
0 Kudos

As this certainly is a differen question, I'd recommend to ask it as a separate question - and you can of course link to this current FAQ there...