Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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.


  • 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

accept rate: 0%

edited 15 Mar '13, 21:14

Mark%20Culp's gravatar image

Mark Culp

Have a look at Parts explosion problems.

permanent link

answered 06 Apr '12, 08:50

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

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

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


(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

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 06 Apr '12, 06:01

question was seen: 4,673 times

last updated: 27 Mar '13, 05:25