Control break reporting using analytical functions
- From: "Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <Oracle-L@xxxxxxxxxxxxx>
- Date: Wed, 13 Jul 2005 10:59:44 -0500
Hey all,
Using 9.2.0.5, we've got this table and data (don't ask about the
datatypes):
CREATE TABLE MYTABLE
(
ASSEMBLY CHAR(15),
OPNO CHAR(7),
ROUTE CHAR(2),
WORKCENTER CHAR(4),
RUNTIME FLOAT(126),
SETUPTIME FLOAT(126)
)/
INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME,
SETUPTIME ) VALUES (
'33858 ', '0995 ', '00', '4003', 0, 2.5);
INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME,
SETUPTIME ) VALUES (
'33858 ', '1000 ', '00', '4003', 1.25, 0);
INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME,
SETUPTIME ) VALUES (
'33858 ', '1004 ', '00', '4003', 0, 0.2);
INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME,
SETUPTIME ) VALUES (
'33858 ', '1005 ', '00', '4003', 0.65, 0);
INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME,
SETUPTIME ) VALUES (
'33858 ', '1010 ', '00', '4051', 0, 0.1);
INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME,
SETUPTIME ) VALUES (
'33858 ', '1020 ', '00', '4091', 1.25, 2.5);
INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME,
SETUPTIME ) VALUES (
'33858 ', '1030 ', '00', '4620', 0, 0);
INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME,
SETUPTIME ) VALUES (
'33858 ', '1040 ', '00', '4052', 0.5, 0.3);
INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME,
SETUPTIME ) VALUES (
'33858 ', '1050 ', '00', '4640', 2, 0);
INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME,
SETUPTIME ) VALUES (
'33858 ', '1055 ', '00', '4003', 0, 5);
INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME,
SETUPTIME ) VALUES (
'33858 ', '1060 ', '00', '4003', 3, 0);
INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME,
SETUPTIME ) VALUES (
'33858 ', '1064 ', '00', '4003', 0, 2);
INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME,
SETUPTIME ) VALUES (
'33858 ', '1065 ', '00', '4003', 0.5, 0);
INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME,
SETUPTIME ) VALUES (
'33858 ', '1070 ', '00', '4006', 1.5, 2);
COMMIT;
The user wants to see the workcenters this assembly travels through, but
without duplicates within each occurrence of that group -- and order is
important! So, instead of:
ASSYPARTNO ROUTINGNO WORKCENTER
33858 00 4003
33858 00 4003 <- Duplicate
33858 00 4003 <- Duplicate
33858 00 4003 <- Duplicate
33858 00 4051
33858 00 4091
33858 00 4620
33858 00 4052
33858 00 4640
33858 00 4003 <- NOT a duplicate
33858 00 4003 <- Duplicate
33858 00 4003 <- Duplicate
33858 00 4003 <- Duplicate
33858 00 4006
...the user wants:
ASSYPARTNO ROUTINGNO WORKCENTER SEQ
33858 00 4003 1
33858 00 4051 2
33858 00 4091 3
33858 00 4620 4
33858 00 4052 5
33858 00 4640 6
33858 00 4003 7
33858 00 4006 8
We got that to work by using some fancy analytical functions:
SELECT assembly, route, workcenter, ROWNUM AS "SEQ"
FROM
(
SELECT DISTINCT assembly, route, workcenter,
CASE WHEN LAG(workcenter) OVER (ORDER BY seqx) !=
workcenter
THEN ROW_NUMBER() OVER (ORDER BY seqx)
ELSE FIRST_VALUE(seqx) OVER (ORDER BY seqx) END
AS "SEQ2"
FROM
(
SELECT assembly, route, workcenter, ROWNUM AS "SEQX"
FROM mytable
ORDER BY opno
)
ORDER BY seq2
)
This works very well (at least in this case), but now we need to SUM the
runtime and setuptime columns for each grouping. The user would like to
see:
ASSYPARTNO ROUTINGNO WORKCENTER SEQ RUNTIME SETUPTIME
33858 00 4003 1 1.9 2.7
33858 00 4051 2 0 0.1
33858 00 4091 3 1.25 2.5
33858 00 4620 4 0 0
33858 00 4052 5 0.5 0.3
33858 00 4640 6 3 7
33858 00 4003 7 0.5 0
33858 00 4006 8 1.5 2
Note how the two groups of "4003" workcenters need to remain separate
and in the correct order, since the assembly will physically be
transported to these workcenters in this order.
Using our CASE...FIRST_VALUE trick, if I could manage a "LAG(seqx,
ROWNUM-FIRST, 1)" type clause, it would be what I'm looking for, but I
haven't had enough SQueaL Lubricant (aka "Beer") to figure that one out
yet.
Sure, this is a simple procedural control break report, but there's got
to be a way to get this output in a SQL statement, doesn't there?
TIA,
Rich
Rich Jesse System/Database Administrator
rich.jesse@xxxxxxxxxxxxxxxxx QuadTech, Sussex, WI USA
Other related posts:
