Re: Can it be done in SQL
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_ordergo
drop table rca_detailgo
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.itemidgo
/* ------------------------------------------------------------- */
/* Now, let's see what we have... */
/* ------------------------------------------------------------- */
select * from rca_detail
order by ordnum, linenumgo
/*
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.linenumgo
/*
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/C++, MFC
Powerbuilder Development, Project Management
--------------------------------------------------