Okay this is a fun one.

I have the following data structure. (Missing most of the data, but
keeping the relational stuff)

PUE_ORITEM - Purchase order item
PK - POR_ID BIGINT Tech key for order.
POI_SEQ INT Item NUmber

(Celko - I know, it would take about 6 months that I don't have to
alter the database and it's not relevant to my query.)

Relates to (one to many)
PUE_ORLINK
PK POR_ID, POI_SEQ, POL_SEQ
Data ORIGIN, ORIGIN_ID, ORIGIN_SEQ

Relates to (many to one) EITHER
PUE_ORSTKALL
PK POR_ID (different order), POS_SEQ

OR
SOE_ORITEM
PK SOR_ID (Sales order), SOI_SEQ

using origin_id and origin_seq depending on Origin (Value of field =
table name)

Now, if the link is to orstkall then there can be another link as
follows

Orstkall relates to pue_orlink as a many to many via POR_ID

So, I have three purchase orders.

PO0001 Item 1 is feeding PO0002 requirement item 1, which then makes
two new items in pue_oritem one of which is linked to sales order
SO0001 item 1.
PO0003 is feeding SO0001 item 2 directly.

Here.

PUE_ORITEM
POR_ID,POI_SEQ,ORD_NO
1,1
2,1
2,2
3,1

PUE_ORDER
POR_ID,ORD_NO
1,PO0001
2,PO0002
3,PO0003

PUE_ORLINK
POR_ID, POI_SEQ, POL_SEQ, ORIGIN, ORIGIN_ID, ORIGIN_SEQ
1,1,1,"PUE_ORSTKALL",2,1
2,1,1,"SOE_ORITEM",1,1
3,1,1,"SOE_ORITEM",1,2

PUE_ORSTKALL
POR_ID,POS_SEQ
2,1

SOE_ORITEM
SOR_ID,SOI_SEQ
1,1
1,2

The chain of PO to PO can go on, theoretically indefinitely
(practically we have sometimes gotten as high as about 10 stages)

I'd like to have a query which, can tell me all the order numbers
involved from a PO item, and another in reverse from a SO item

so, given PO0001 item 1 the result set is

PO0003
SO0001

given PO0003 item 1 the result set is
SO0001

and the other query, given SO0001 item 1 is
PO0002
PO0003
PO0001,
potentially with some clue as to the depth of the chain.

I hope this is understandable.

Iain

Please post DDL for these tables and their constraints...

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 .

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Fri, 28 Mar 2008 11:31:51 -0700 (PDT), --CELKO--
<jcelko212@earthlink.net> wrote:

>>> P.S. Where's the metadata? <<
>
>Table names inside a table at the same level of abstraction.

And your solution to this?

>> And your solution to this? <<

One of the hierarchical designs given in TREES & HIERARCHIES IN SQL
(ISBN: 978-1-55860-920-4). I happen to like the nested sets model
because it ports and gives very easy hierarchical aggregations (BOM,
accounting reports, etc as opposed to path searches).

You might also want to look at http://www.dbazine.com/ofinterest/oi...archterm=Celko

The article gives a declarative way to enforce Transition Constraints,
which might if your process steps have to come in some order
("bake the beard before you slice it", etc.)

On Sat, 29 Mar 2008 15:55:17 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> 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 .
>
>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. <G> 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.

On Mon, 31 Mar 2008 10:28:57 -0700 (PDT), --CELKO--
<jcelko212@earthlink.net> wrote:

>>> And your solution to this? <<
>
>One of the hierarchical designs given in TREES & HIERARCHIES IN SQL
>(ISBN: 978-1-55860-920-4). I happen to like the nested sets model
>because it ports and gives very easy hierarchical aggregations (BOM,
>accounting reports, etc as opposed to path searches).
>
>You might also want to look at http://www.dbazine.com/ofinterest/oi...archterm=Celko
>
>The article gives a declarative way to enforce Transition Constraints,
>which might if your process steps have to come in some order
>("bake the beard before you slice it", etc.)


I have looked at the article, but I don't think the dataset described
is a match for my problem. I have two different entities with
differing requirements for much of the data held within them, that
have the same effect on one part of the system.

A sales line item is comparable with a production stock requirement
item only at the level where it effects the allocation of physical and
incoming material (the case described here). The other data required
to be held for each of these items is radically different. As is the
order header information for sales and procurement orders. I am unsure
where this fits in a hierachical data structure.

Iain Sharp (iains@pciltd.co.uk) writes:
> Reaching SOE_ORITEM is always an end of the chain. <G> 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 , 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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx