Re: Can it be done in SQL
Thank you Rusty.
This is exactly what I was looking for.
Thanks again,
toby denbow
In article <31E426B7.3281@phoenix.net>, Rusty Alderson
<cessna@phoenix.net> wrote:
> Toby,
>
> Here is an illustration of a technique which will accomplish your goal.
> Note that I am using Transact SQL (Microsoft & Sybase) so you may need
> to tweak it for Informix (which I assume you are using.) Sorry, I have
> no experience with Informix - don't know if you can create temp tables
> in Informix, but, if not, this can be worked around. I am only using
> the temp table as a source for the detail records for illustration
> purposes only. The basic technique should work in virtually any flavor
> of SQL:
>
> /* ------------------------------------------------------------- */
> /* Tested using MS SQL-Server v.4.21a */
> /* ------------------------------------------------------------- */
>
> use pubs
> go
>
> drop table rca_order> go
>
> drop table rca_detail> go
>
> drop table #detail
> go
>
> /* ------------------------------------------------------------- */
> /* Create the tables we'll need to illustrate the concept. */
> /* ------------------------------------------------------------- */
>
> create table rca_order
> (
> ordnum int not null
> )> go
>
> create table rca_detail
> (
> ordnum int not null,
> linenum int not null,
> itemid int not null,
> qty smallint not null,
> amt float not null
> )> go
>
> /* ------------------------------------------------------------- */
> /* This is our current order (#12345) */
> /* ------------------------------------------------------------- */
>
> insert rca_order
> values (12345)
> go
>
> /* ------------------------------------------------------------- */
> /* All detail will go into a temp table (for this illustration) */
> /* ------------------------------------------------------------- */
>
> create table #detail
> (
> ordnum int,
> itemid int,
> qty smallint,
> amt float
> )
> go
>
> /* ------------------------------------------------------------- */
> /* I don't know how detail records will be generated. This */
> /* assumes that the detail records may not come from the same */
> /* source, or be generated at the same time. When you know you */
> /* have all of the detail records, issue the final insert. */
> /* */
> /* First populate the temp table (you may be able to eliminate */
> /* this step, if all detail records come from the same source, */
> /* at the same time.) */
> /* ------------------------------------------------------------- */
>
> insert #detail(ordnum, itemid, qty, amt)
> values (12345, 46551, 2, 100.98)
> go
>
> insert #detail(ordnum, itemid, qty, amt)
> values (12345, 10019, 1, 21.00)
> go
>
> insert #detail(ordnum, itemid, qty, amt)
> values (12345, 664, 4, 18.50)
> go
>
> insert #detail(ordnum, itemid, qty, amt)
> values (12345, 2010, 200, 0.44)
> go
>
> /* -------------------------------------------------------------- */
> /* Now we'll insert detail for a second order (#22115) just to */
> /* show that this technique will work even if you combine orders */
> /* in the temp table (or what ever your source for detail will be */
> /* -------------------------------------------------------------- */
>
> insert #detail(ordnum, itemid, qty, amt)
> values (22115, 46551, 3, 100.98)
> go
>
> insert #detail(ordnum, itemid, qty, amt)
> values (22115, 10019, 2, 21.00)
> go
>
> insert #detail(ordnum, itemid, qty, amt)
> values (22115, 8864, 1, 99.0)
> go
>
> insert #detail(ordnum, itemid, qty, amt)
> values (22115, 201, 12, 1.55)
> go
>
> insert #detail(ordnum, itemid, qty, amt)
> values (22115, 991, 10, 15.25)
> go
>
> /* ------------------------------------------------------------- */
> /* Now, if the detail records are all present and accounted for: */
> /* ------------------------------------------------------------- */
>
> /* FINAL INSERT */
>
> insert rca_detail
> select x.ordnum, count(*), x.itemid, x.qty, x.amt
> from #detail x,
> #detail y
> where y.itemid <= x.itemid
> and y.ordnum = x.ordnum
> group by x.ordnum, x.itemid> go
>
> /* ------------------------------------------------------------- */
> /* Now, let's see what we have... */
> /* ------------------------------------------------------------- */
>
> select * from rca_detail
> order by ordnum, linenum> go
>
> /*
>
> Results:
>
> ordnum linenum itemid qty amt
> ----------- ----------- ----------- ------ ------------------------
> 12345 1 664 4 18.5
> 12345 2 2010 200 0.44
> 12345 3 10019 1 21.0
> 12345 4 46551 2 100.98
> 22115 1 201 12 1.55
> 22115 2 991 10 15.25
> 22115 3 8864 1 99.0
> 22115 4 10019 2 21.0
> 22115 5 46551 3 100.98
>
> (9 row(s) affected)
>
> */
>
> /* ------------------------------------------------------------- */
> /* Other neat tricks can be done with a similar technique: */
> /* */
> /* Let's compute a running total for each order (not practical */
> /* for this particular application, perhaps, but very useful in */
> /* other situations! */
> /* ------------------------------------------------------------- */
>
> select x.ordnum, x.linenum, Extension=(x.amt * x.qty),
Cumulative=sum(y.qty * y.amt)
> from rca_detail x,
> rca_detail y
> where y.itemid <= x.itemid
> and y.ordnum = x.ordnum
> group by x.ordnum, x.itemid, x.amt
> order by x.ordnum, x.linenum> go
>
> /*
>
> Result:
>
> ordnum linenum Extension Cumulative
> ----------- ----------- ------------------------ ------------------------
> 12345 1 74.0 74.0
> 12345 2 88.0 162.0
> 12345 3 21.0 183.0
> 12345 4 201.96 384.96
> 22115 1 18.6 18.6
> 22115 2 152.5 171.1
> 22115 3 99.0 270.1
> 22115 4 42.0 312.1
> 22115 5 302.94 615.04
>
> (9 row(s) affected)
>
> */
>
> HTH
> --Rusty
>
> --------------------------------------------------
> Rusty Alderson, RCA Client/Server Solutions:
> Sybase DBA, DB-Library Development, C