Re: Recursive Queries?

Iain Sharp (iains@pciltd.co.uk) 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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Recursive Queries?

On Sat, 29 Mar 2008 15:55:17 +0000 (UTC), Erland Sommarskog
wrote:

>Iain Sharp (iains@pciltd.co.uk) 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?

Reaching SOE_ORITEM is always an end of the chain. Sometimes
however, the end of the chain is with PUE_ORITEM, in the case of
producing pieces for stock.

I think that part of the confusion is that I used the same technical
key values for both sales and procurement orders (1,1 and 1,1)

The links here are from PO0001 to PO0002 and from PO0002 to SO0001 as
one chain and from PO0003 to SO0001 as a second chain.

In practice, there could be multiple stock requirements feeding one
production order. Some from stock, and some preallocating incoming
material so this dataset, confusing as it is, details the two simplest
forms of this transaction we encounter.

I did write a query to check that this data worked, but it required
the same entities aliased twice to get the data, and the same
technique would require three aliases for another level of data and so
on.

In the 4GL I use, the links are 'easy' to explore recursively, but I
just can't see how to do the same in SQL 2000.

Re: Recursive Queries?

Iain Sharp (iains@pciltd.co.uk) writes:
> Reaching SOE_ORITEM is always an end of the chain. Sometimes
> however, the end of the chain is with PUE_ORITEM, in the case of
> producing pieces for stock.
>
> I think that part of the confusion is that I used the same technical
> key values for both sales and procurement orders (1,1 and 1,1)

The repetition of 1, 1 did certainly not help, nor did the cryptic
column names. The amount of data was also a bit meagre.

Below are queries for what you asked for. There is both an SQL 2005
version using recursive CTEs and there is an iterative solution using
a temp table for SQL 2000. As you may guess I first wrote the SQL 2005
solution, and then derived the SQL 2000 solution from that.

See them as conceptual illustrations of how you could work this sort of
data, but beware that they may not be fully correct, as I had a hard
time with the column names. Some of your tables do not appear in the
queries at all, but maybe they should.

WITH recurs AS (
SELECT pol_por_id, pol_poi_seq, pol_origin,
pol_org_id, pol_org_seq
FROM pue_orlink
WHERE pol_por_id = 2
AND pol_poi_seq = 1
UNION ALL
SELECT p.pol_por_id, p.pol_poi_seq, p.pol_origin,
p.pol_org_id, p.pol_org_seq
FROM pue_orlink p
JOIN recurs r ON p.pol_por_id = r.pol_org_id
AND p.pol_poi_seq = r.pol_org_id
WHERE r.pol_origin = 'PUE_ORSTKALL'
)
SELECT p.por_ord_no
FROM recurs r
JOIN pue_order p ON r.pol_por_id= p. por_id
WHERE r.pol_origin = 'PUE_ORSTKALL'
UNION ALL
SELECT s.sor_ord_no
FROM recurs r
JOIN soe_order s ON r.pol_org_id = s.sor_id
WHERE r.pol_origin = 'SOE_ORITEM'
go
DECLARE @lvl int
SELECT @lvl = 1

SELECT pol_por_id, pol_poi_seq, pol_origin, pol_org_id,
pol_org_seq, lvl = @lvl
INTO #recurs
FROM pue_orlink
WHERE pol_por_id = 2
AND pol_poi_seq = 1

WHILE @@rowcount > 0
BEGIN
SELECT @lvl = @lvl + 1
INSERT #recurs(pol_por_id, pol_poi_seq, pol_origin,
pol_org_id, pol_org_seq, lvl)
SELECT p.pol_por_id, p.pol_poi_seq, p.pol_origin,
p.pol_org_id, p.pol_org_seq, @lvl
FROM pue_orlink p
JOIN #recurs r ON p.pol_por_id = r.pol_org_id
AND p.pol_poi_seq = r.pol_org_id
WHERE r.pol_origin = 'PUE_ORSTKALL'
AND r.lvl = @lvl -1
END

SELECT p.por_ord_no
FROM #recurs r
JOIN pue_order p ON r.pol_por_id= p. por_id
WHERE r.pol_origin = 'PUE_ORSTKALL'
UNION ALL
SELECT s.sor_ord_no
FROM #recurs r
JOIN soe_order s ON r.pol_org_id = s.sor_id
WHERE r.pol_origin = 'SOE_ORITEM'

drop table #recurs
go
WITH recurs AS (
SELECT p.pol_por_id, p.pol_poi_seq, p.pol_origin,
p.pol_org_id, p.pol_org_seq
FROM pue_orlink p
JOIN soe_oritem s ON p.pol_org_id = s.soi_sor_id
AND p.pol_org_seq = s.soi_seq
WHERE s.soi_sor_id = 1
UNION ALL
SELECT p.pol_por_id, p.pol_poi_seq, p.pol_origin,
p.pol_org_id, p.pol_org_seq
FROM pue_orlink p
JOIN recurs r ON r.pol_por_id = p.pol_org_id
AND r.pol_poi_seq = p.pol_org_id
WHERE p.pol_origin = 'PUE_ORSTKALL'
)
SELECT DISTINCT child = coalesce(s.sor_ord_no, p1.por_ord_no),
parent = p2.por_ord_no
FROM recurs r
LEFT JOIN soe_order s ON r.pol_origin = 'SOE_ORITEM'
AND r.pol_org_id = s.sor_id
LEFT JOIN pue_order p1 ON r.pol_origin = 'PUE_ORSTKALL'
AND r.pol_org_id = p1.por_id
JOIN pue_order p2 ON r.pol_por_id = p2.por_id
go
DECLARE @lvl int
SELECT @lvl = 1

SELECT p.pol_por_id, p.pol_poi_seq, p.pol_origin,
p.pol_org_id, p.pol_org_seq, lvl = @lvl
INTO #recurs
FROM pue_orlink p
JOIN soe_oritem s ON p.pol_org_id = s.soi_sor_id
AND p.pol_org_seq = s.soi_seq
WHERE s.soi_sor_id = 1

WHILE @@rowcount > 0
BEGIN
SELECT @lvl = @lvl + 1

INSERT #recurs(pol_por_id, pol_poi_seq, pol_origin,
pol_org_id, pol_org_seq, lvl)
SELECT p.pol_por_id, p.pol_poi_seq, p.pol_origin,
p.pol_org_id, p.pol_org_seq, @lvl
FROM pue_orlink p
JOIN #recurs r ON r.pol_por_id = p.pol_org_id
AND r.pol_poi_seq = p.pol_org_id
WHERE p.pol_origin = 'PUE_ORSTKALL'
AND r.lvl = @lvl - 1
END

SELECT DISTINCT child = coalesce(s.sor_ord_no, p1.por_ord_no),
parent = p2.por_ord_no
FROM #recurs r
LEFT JOIN soe_order s ON r.pol_origin = 'SOE_ORITEM'
AND r.pol_org_id = s.sor_id
LEFT JOIN pue_order p1 ON r.pol_origin = 'PUE_ORSTKALL'
AND r.pol_org_id = p1.por_id
JOIN pue_order p2 ON r.pol_por_id = p2.por_id

DROP TABLE #recurs



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx