Re: Recursive Queries?

On Thu, 27 Mar 2008 22:48:47 +0000 (UTC), Erland Sommarskog
<esquel@xxxxxxxxxxxxx> wrote:

Iain Sharp (iains@xxxxxxxxxxxx) writes:
I hope this is understandable.

I will have to admit that I got lost somewhere in the middle. It would
help if you posted:

o CREATE TABLE statements for your tables, including constraints.
o INSERT statements with sample data.
o The desired output given the sample.

Yes, I see that you have sample data and result in your post, but if you
have it all in SQL it's easier to copy and paste into a query window to
develop a tested query.

But most of all, we need to know which version of SQL Server you are using.
SQL 2005 has syntax to support recursive queries, whereas in SQL 2000
you need to run the recursion yourself.


Okay, here goes. I cut the tables down considerably in the original
description. Will attempt to do so for the create tables etc.
Best to go for SQL 2000 code, the application is installed on both
types.


-----------------------------------------------------------------------

CREATE TABLE [pue_order] (
[por_id] [int] NOT NULL ,
[por_ord_no] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
CONSTRAINT [pue_order_pk] PRIMARY KEY CLUSTERED
(
[por_id]
)

)
CREATE UNIQUE INDEX [pue_orderI2] ON [pue_order]([por_ord_no])

INSERT pue_order VALUES (1,'PO0001')
INSERT pue_order VALUES (2,'PO0002')
INSERT pue_order VALUES (3,'PO0003')



CREATE TABLE [pue_oritem] (
[poi_por_id] [int] NOT NULL ,
[poi_seq] [int] NOT NULL
CONSTRAINT [pue_oritem_pk] PRIMARY KEY CLUSTERED
(
[poi_por_id],
[poi_seq]
)
) ON [PRIMARY]


INSERT pue_oritem VALUES ( 1, 1)
INSERT pue_oritem VALUES ( 1, 2)
INSERT pue_oritem VALUES ( 2, 1)
INSERT pue_oritem VALUES ( 3, 1)

CREATE TABLE [pue_orlink] (
[pol_por_id] [int] NOT NULL ,
[pol_poi_seq] [int] NOT NULL ,
[pol_seq] [int] NOT NULL ,
[pol_origin] [char] (16) NULL ,
[pol_org_id] [int] NULL ,
[pol_org_seq] [int] NULL

CONSTRAINT [pue_orlink_pk] PRIMARY KEY CLUSTERED
(
[pol_por_id],
[pol_poi_seq],
[pol_seq]
)
)

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)

CREATE TABLE [pue_orstkall] (
[pos_por_id] [int] NOT NULL ,
[pos_seq] [int] NOT NULL
CONSTRAINT [pue_orstkall_pk] PRIMARY KEY CLUSTERED
(
[pos_por_id],
[pos_seq]
)
)

INSERT 'PUE_ORSTKALL' VALUES (1,1)


CREATE TABLE [soe_order] (
[sor_id] [int] NOT NULL ,
[sor_ord_no] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
CONSTRAINT [soe_order_pk] PRIMARY KEY CLUSTERED
(
[sor_id]
)
)
CREATE UNIQUE INDEX [soe_orderI2] ON [soe_order]([sor_ord_no])

INSERT soe_order VALUES (1,'SO0001')


CREATE TABLE [soe_oritem] (
[soi_sor_id] [int] NOT NULL ,
[soi_seq] [int] NOT NULL
CONSTRAINT [soe_oritem_pk] PRIMARY KEY CLUSTERED
(
[soi_sor_id],
[soi_seq]
)
)

INSERT soe_oritem VALUES (1,1)
INSERT soe_oritem VALUES (1,2)

-------------------------------------------------------------------------------

pue_order relates to pue_oritem one to many on por_id = pol_por_id
pue_order relates to pue_orstkall one to many on por_id = pos_por_id
pue_oritem relates to pue_orlink one to many on poi_por_id =
pol_por_id and poi_seq = pol_poi_seq

pue_orlink relates to pue_orstkall many to one on pol_origin =
'PUE_ORSTKALL', pol_org_id = pos_por_id, pol_org_seq = pos_seq
and to soe_oritem many to one on
pol_origin='SOE_ORITEM',pol_org_id=soi_sor_id,pol_org_seq=soi_seq
and finally,
soe_order relates to soe_oritem one to many on sor_id = soi_sor_id

---------------------------------------------------------------------------------
Two different queries, where por_id = 2 and poi_seq = 1, find all
orders 'upwards' in the chain, Resultset
'PO0001'
'SO0001'

from the other end, where sor_id = 1 and soi_seq = 1, find all orders
(and parents?) 'downwards' in the chain. Resultset
Order..|..Parent
'PO0001'|'SO0001'
'PO0002'|'PO0001'
'PO0003'|'SO0001'

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.

Iain


.

Relevant Pages

  • Re: Query to extract the latest version of a record
    ... Chris: Wow! ... > The origianl SQL, realigned for readability. ... > above because those columns were not included in the sample data. ... > The table name is lengthy, so I used table aliases. ...
    (microsoft.public.access.queries)
  • Re: Linking records together
    ... the sample data is too easy. ... We need to track work items across several existing applications. ... SQL> create table event_track( ... SQL> column start_item format a10 ...
    (comp.databases.oracle.misc)
  • Re: Query to extract the latest version of a record
    ... > I have an audit table set up from which I want to extract the ... > What I want to do is extract the latest "EditTo"s for all records ... The origianl SQL, realigned for readability. ... above because those columns were not included in the sample data. ...
    (microsoft.public.access.queries)
  • Re: Help! Grouping in Pivot Table
    ... It might help if you post the SQL of your actual Queries (maybe along with sample data from your Table). ... I have a count of all the records and a field showing the age of the file. ...
    (microsoft.public.access.queries)
  • Re: Selecting rows from a distinct selection of one field
    ... Please post DDL + sample data + desired result. ... > I'm not that new to SQL, but still I can't figure this out. ... > I'm writing some routines to check through IIS logs. ...
    (microsoft.public.sqlserver.programming)