How to write a recursive query?
-
I am wondering if there is some type of recursive query to return the values I want from the following database.
Here is the setup:
The client builds reptile cages.
Each cage consists of aluminum framing, connectors to connect the aluminum frame, and panels to enclose the cages. In the example below, we are not leaving panels out to simplify things. We are also not concerned with the dimensions of the cage.
The PRODUCT table contains all parts in inventory. A finished cage is also considered a PRODUCT. The PRODUCT table is recursively joined to itself through the ASSEMBLY table.
PRODUCTS that consist of a number of PRODUCTS are called an ASSEMBLY. The ASSEMBLY table tracks what PRODUCTS are required for the ASSEMBLY.
Sample database can be downloaded from http://www.handlerassociates.com/cage_configurator.mdb
Here is a quick schema:
Table: PRODUCT
--------------------------
PRODUCTID PK
PRODUCTNAME nVarChar(30)
Table: ASSEMBLY
--------------------------
PRODUCTID PK (FK to PRODUCT.PRODUCTID)
COMPONENTID PK (FK to PRODUCT.PRODUCTID)
QTY INT
I can write a query that takes the PRODUCTID, and returns all
PRODUCT
=======
PRODUCTID PRODUCTNAME
--------- -----------
1 Cage Assembly - Solid Sides
2 Cage Assembly - Split Back
3 Cage Assembly - Split Sides
4 Cage Assembly - Split Top/Bottom
5 Cage Assembly - Split Back and Sides
6 Cage Assembly - Split Back and Top/Bottom
7 Cage Assembly - Split Back and Sides and Top/Bottom
8 33S - Aluminum Divider
9 33C - Aluminum Frame
10 T3C - Door Frame
11 Connector Kit
12 Connector Socket
13 Connector Screws
ASSEMBLY
=========
PRODUCTID COMPONENT QTY
--------- --------- ---
1 9 8
1 10 4
1 11 1
2 1 1
2 8 1
3 1 1
3 8 1
4 1 1
4 8 1
5 1 1
5 8 2
6 1 1
6 8 2
7 1 1
7 8 3
11 12 8
11 13 8
I need a query that will give me all parts for each PRODUCT.
Example: I want all parts for the PRODUCT "Cage Assembly - Split Back"
The results would be:
PRODUCTID PRODUCTNAME
--------- -----------
2 Cage Assembly - Split Back
1 Cage Assemble - Solid Back
9 33C - Aluminum Frame
10 T3C - Door Frame
11 Connector Kit
8 33S - Aluminum Divider
12 Connector Socket
13 Connector Screws
Is it possible to write such a query or stored procedure?
-
Hi Robin,
I solved a similar problem recently (dealing with matching colors), so it was much easier this time. I think that a recursion is not the best choice to handle these thing in SQL Server, due to the limited number of calls (the depth of the call tree). Fortunatelly, there is an equivalent (and I hope a better one) iterative solution, given below.
create function dbo.udfProduct_Parts (@product_id int) returns @res table (product_id int not null primary key) as begin insert into @res (product_id) values (@product_id) while (@@ROWCOUNT > 0) begin insert into @res (product_id) select distinct COMPONENTID from (select COMPONENTID from @res as r inner join ASSEMBLY on (ASSEMBLY.PRODUCTID = r.product_id) ) as tmp where not exists (select product_id from @res as r where (r.product_id = tmp.COMPONENTID)) end return end goThe idea here is to incrementally add new products, detected as parts of existing products in the temporary result table. The process is guaranteed to finish, since it checks the number of rows added in the previous iteration. When no new products are added, the loop exits.
In SQL statements, you can use the UDF like this:
select p.product_id, pr.PRODUCTNAME from dbo.udfProduct_Parts(2) as p inner join PRODUCT as pr on (pr.PRODUCTID = p.product_id) order by p.product_id
I hope this will help you. It requires MS SQL Server 2000, but an equivalent stored procedure can be written if you have MS SQL 7 or older version.
I haven't read the book by Joe Celko, but I am REALLY interested in his oppinion to the approach I presented, compared to other approaches (I am sure it is already described in the book).
Regards,
Goce Smilevski.
-
The SQL syntax to support recursion directly in SQL does not exist in SQL2000, but is (finally) introduced in SQL2005. Some other DBMSs have had recursion for years. The DB2 SQL Cookbook has a lot of advise on how to play with native SQL recursion that will apply to SQL2005 as the syntax is virtually the same.
I think BOL has a section on how you can get recursive results in SQL2000 using a SP, but this is unlikely to be much of an improvement on the previous replies.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
You can also write a recursive user defined function that returns a table. I used that for a bill of material explosion. Found the code on the Net.
CREATE function dbo.GetComponents( @MfgItem varchar(47),
@CurrentDate datetime,
@StartLevel int,
@IncludeParent bit )
returns @retFindComponents table ( t_seqn int identity(1,1),
t_mitm varchar(47),
t_sitm varchar(47),
t_levl int,
t_pono int,
t_qana real,
t_cpha int )
as
begin
if (@IncludeParent=1) -- To include the mfg item in the output table.
begin
insert @retFindComponents
( t_mitm,t_sitm,t_levl,t_pono, t_qana, t_cpha )
select distinct ' Top Item',t_mitm,0,0, 1, 2
from ttibom010101
where t_mitm=@MfgItem and ( (@CurrentDate >= t_indt and @CurrentDate <= t_exdt ) or
@CurrentDate >= t_indt and t_exdt = '01-01-1970')
end
declare @ParentItem varchar(47),
@ComponentID varchar(47),
@Level int,
@Position int,
@BomQty real,
@IsPhantom int
set @Level = @StartLevel -- Starting point for the Bom level - relative to each calling of the fn
declare RetrieveComponents cursor static local for
select t_mitm, t_sitm,@Level, t_pono, t_qana, t_cpha
from ttibom010101
where t_mitm=@MfgItem and ( (@CurrentDate >= t_indt and @CurrentDate <= t_exdt ) or
@CurrentDate >= t_indt and t_exdt = '01-01-1970')
open RetrieveComponents
fetch next from RetrieveComponents
into @ParentItem,@ComponentID, @Level, @Position, @BomQty,@IsPhantom
while (@@fetch_status = 0) -- loops to get all the inner mfg item components from the components
begin
set @Level = @Level + 1
insert @retFindComponents
( t_mitm, t_sitm ,t_levl, t_pono, t_qana, t_cpha)
values (@ParentItem, @ComponentID, @Level, @Position, @BomQty, @IsPhantom )
insert @retFindComponents
( t_mitm,t_sitm,t_levl,t_pono, t_qana, t_cpha)
select t_mitm,t_sitm,t_levl,t_pono, t_qana, t_cpha from dbo.GetComponents( @ComponentID,@CurrentDate, @Level,0 )
fetch next from RetrieveComponents
into @ParentItem,@ComponentID, @Level,@Position, @BomQty, @IsPhantom
end
close RetrieveComponents
deallocate RetrieveComponents
return
end
-
I like Goce's solution. Be aware that often a recursion or iteration will never end if someone puts a bad assembly in your database:
Product 1 uses Component 2, QTY = 1
Product 2 uses Componenet 1, QTY = 1
Goce's solution avoids the eternal loop: To build BOM for product 1,
- it inserts 1 into the result table,
- then in the first iteration it adds "component" 2.
- The next iteration, it finds a candidate "component" 1 to insert, but it detects that "component" 1 is already in the result table, and inserts nothing.
Then it exits.
In other iterative/recursive situations, it may not be so clear that the process will end. A simple safety net is to limit the iteration to some reasonably high maximum.
Regards,Bob Monahon
-
Thanks, Goce. That is a very elegent solution!
-
Hey Yvan, is that for SQL Server 2000?
Is there a way to do the same thing on 97?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply