Re: Recursive Queries?
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sat, 29 Mar 2008 15:55:17 +0000 (UTC)
Iain Sharp (iains@xxxxxxxxxxxx) writes:
I appreciate that this is confusing, you should try it with the
quantities and statuses of the relevant orders included.
Basically we are modelling the pre-allocation of stock from purchase,
through 0-n processing jobs, into sales. We store both purchase and
processing jobs in pue_order, pue_oritem and pue_orstkall (stock
requirements), and sales in soe_order and soe_oritem.
It's indeed confusing and the cryptic column names do not help.
The heart of the matter seems to be the the pue_orlink table:
INSERT pue_orlink VALUES (1,1,1,'SOE_ORITEM',1,1)
INSERT pue_orlink VALUES (3,1,1,'SOE_ORITEM',1,2)
INSERT pue_orlink VALUES (2,1,1,'PUE_ORSTKALL',1,1)
I was to told to start with (2, 1) which refers to the first and third
columns if I understand it right. From there I can find (1, 1) in the
last two columns, then find that row in pue_orlink. Again I find (1, 1)
in the last two column, but since I am now at a SUE_ORITEM, I have reached
the end of the chain? That is, a sales order can be related to another
sales order, but a sales order can lead to one or more purchase order
that can lead to new purchase orders and so on?
Generally, in SQL 2005 you can in most cases wind up (or down) a hierarchial
set of data with a recursive CTE (Common Table Expression). A CTE as such
takes the form:
WITH cte_name (columlist) AS (
SELECT ...
)
You put this first in a query, and you can then refer to the CTE in
rest of the query as if it was a table. Very similar to derived tables,
but they have a name. I should add that the column-list is optional,
and that the columns also can be defined from the SELECT statement
A recursive CTE has a special form:
WITH cte_name AS (
SELECT ...
UNION ALL
SELECT ...
FROM cte_name
...
)
It's a query with a UNION ALL where the second part of the UNION ALL
refers to the CTE itself.
In SQL 2000 ther is no query construct like this, but recursive structures
needs to be handled one level at a time. Typically you assemble data in
a temp table along the way.
I regret that I cannot give you a query, despite your offering of
CREATE TABLE and INSERT statements. But there are still too many pieces
missing for me.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Recursive Queries?
- From: Iain Sharp
- Re: Recursive Queries?
- From: Erland Sommarskog
- Re: Recursive Queries?
- From: Iain Sharp
- Recursive Queries?
- Prev by Date: Re: Violation of primary key
- Next by Date: Re: How to Interrogate SQL Server Tables for Specific Values - Here's how to do it in MS Access
- Previous by thread: Re: Recursive Queries?
- Next by thread: Re: Recursive Queries?
- Index(es):
Relevant Pages
|
