The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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

asked 06 Apr '12, 06:01

Kongthap's gravatar image

Kongthap
110246
accept rate: 0%

edited 15 Mar '13, 21:14

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262


Have a look at Parts explosion problems.

permanent link

answered 06 Apr '12, 08:50

Breck%20Carter's gravatar image

Breck Carter
26.6k418575824
accept rate: 21%

Wow. Save that one for your next book. :)

(07 Apr '12, 12:19) dejstone
Replies hidden

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 :)

(07 Apr '12, 17:51) Breck Carter

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.

(08 Apr '12, 05:57) Volker Barth
2

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

Example: RECURSIVE UNION

(11 Apr '12, 07:37) Volker Barth

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...?!

permanent link

answered 27 Mar '13, 05:20

Irakli's gravatar image

Irakli
12
accept rate: 0%

edited 27 Mar '13, 05:21

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...

(27 Mar '13, 05:25) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×100
×90
×46
×20

question asked: 06 Apr '12, 06:01

question was seen: 2,553 times

last updated: 27 Mar '13, 05:25