Control break reporting using analytical functions

From: Jesse, Rich <Rich.Jesse_at_quadtechworld.com>
Date: Wed, 13 Jul 2005 10:59:44 -0500
Message-ID: <FB5D3CCFCECC2948B5DCF4CABDBE66971FFAB7@QTEX1.qg.com>

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_at_quadtechworld.com      QuadTech, Sussex, WI USA

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 13 2005 - 11:06:26 CDT