Reports returning cartesian product

[wsam]

I am trying to write two reports:

One: Retrieves the first record for each unit for each invoice number. Each unit, may have been 'ENR', 'AT DEST', 'AV' many times, and each invoice may have several units associated to it (see example below). As you can see, I do get a small cartesian product if a unit has been 'ENR','AT DEST','AV' several times while working on the same invoice. I have noticed that the first occurance of each unit record is bringing back the correct information for the first set of 'DSP' to 'AV' records for each unit, and would like to not get the rest.

Two: Want to get the correct records of the unit on an invoice that has made several deliveries for the same invoice without getting a cartesian product.

What do I need to put in my statements for this would occur? I am using the same basic select SQL statement for both reports. Thanks in advance!!!!

SELECT
A.INVOICE,
TO_CHAR(A.ENTRYDATE,'MM/DD/YYYY') "DATE" ,
D.UNIT,
(E.STATUSDATE-D.STATUSDATE)*24*60*60 AS DSPENR,
(F.STATUSDATE-E.STATUSDATE)*24*60*60 AS ENRATDST,
(G.STATUSDATE-F.STATUSDATE)*24*60*60 AS ATDSTAV,
A.DESCR
FROM
T1_HEADER A,
T2_STATUS D,
T2_STATUS E,
T2_STATUS F,
T2_STATUS G
WHERE
A.ENTRYDATE >= TO_DATEFROM_DT,'MM/DD/YYYY') AND
A.ENTRYDATE <= TO_DATETO_DT,'MM/DD/YYYY') AND
A.INVOICE = D.INVOICE AND
A.INVOICE = E.INVOICE AND
A.INVOICE = F.INVOICE AND
A.INVOICE = G.INVOICE AND
D.STATUSNAME = 'DSP' AND
E.STATUSNAME = 'ENR' AND
F.STATUSNAME = 'AT DEST' AND
G.STATUSNAME = 'AV' AND
GROUP BY
A.INVOICE, TO_CHAR(A.ENTRYDATE,'MM/DD/YYYY'), D.UNIT ,
(D.STATUSDATE-H.STATUSDATE)*24*60*60 ,(E.STATUSDATE-D.STATUSDATE)*24*60*60,
(F.STATUSDATE-E.STATUSDATE)*24*60*60,(G.STATUSDATE-F.STATUSDATE)*24*60*60,
A.DESCR,TO_NUMBER(TO_CHAR(ENTRYDATE,'SSSSS'))
ORDER BY A.INVOICE,D.UNIT


INVOICE #DATE D.UNIT DSPENR ENRATDST ATDSTAV

0308112 05/07/03 D13 0 126 12760
0308112 05/07/03 D13 0 1668 12760
0308112 05/07/03 D13 0 1668 11218
0308112 05/07/03 D13 0 126 11218
0308112 05/07/03 D13 0 9012 3874
0308112 05/07/03 D15 7181 1120 15243
0308112 05/07/03 D15 7181 5267 11096
0308112 05/07/03 D15 7181 15277 1086
0308112 05/07/03 D15 7551 750 15243
0308112 05/07/03 D15 7551 4897 11096
0308112 05/07/03 D15 7551 14907 1086
0308112 05/07/03 D15 11416 1032 11096
0308112 05/07/03 D15 11416 11042 1086
0308112 05/07/03 D15 942 371 7218
0308112 05/07/03 D19 0 26 3491
0308112 05/07/03 D19 0 413 3104
0308112 05/07/03 D22 725 3497 12302
0308112 05/07/03 D22 725 5595 10204
0308112 05/07/03 D22 725 10039 5760
0308112 05/07/03 D22 725 10965 4834
0308112 05/07/03 D22 3246 3074 10204
0308112 05/07/03 D22 3246 7518 5760
0308112 05/07/03 D22 3246 8444 4834
0308112 05/07/03 D22 11362 328 4834

 

[carp]

Please post a sample of some of the t2_status table (a few rows from a couple of invoices would help).

 

[wsam]

Typical invoice records in T2 are:

INVOICE OCCCNT SEGMENTNAME SEGMENTDATE UNIT

031876 1001 DSP 01/03/03 02:15 E43
031876 1002 ATDST 01/03/03 02:15 E43
031876 1003 AV 01/03/03 02:27 E43
031882 1000 ENTRY 01/03/03 02:21
031882 1001 DSP 01/03/03 02:23 D23
031882 1002 ENR 01/03/03 02:23 D23
031882 1003 ENR 01/03/03 02:25 D23
031882 1004 ATDST 01/03/03 02:25 D23
031882 1005 AV 01/03/03 02:51 D23
031882 2001 DSP 01/03/03 02:23 D73
031882 2002 ENR 01/03/03 02:23 D73
031882 2003 ENR 01/03/03 02:25 D73
031882 2004 ENR 01/03/03 02:25 D73
031882 2005 AV 01/03/03 02:35 D73
031882 3001 DSP 01/03/03 02:58 D52
031882 3002 ENR 01/03/03 02:58 D52
031882 3003 AV 01/03/03 02:58 D52
031900 1000 ENTRY 01/03/03 03:15
031900 1001 DSP 01/03/03 03:28 E23
031900 1002 ENR 01/03/03 03:28 E23
031900 1003 ATDST 01/03/03 03:42 E23
031900 1004 AV 01/03/03 03:42 E23
031900 2001 DSP 01/03/03 03:29 D67
031900 2002 ENR 01/03/03 03:29 D67
031900 2003 ATDST 01/03/03 03:35 D67
031900 2004 ENR 01/03/03 03:40 D67
031900 2005 AV 01/03/03 03:40 D67
031900 3001 DSP 01/03/03 03:30 A45
031900 3002 ENR 01/03/03 03:30 A45
031900 3003 ATDST 01/03/03 03:39 A45
031900 3004 ENR 01/03/03 03:42 A45
031900 3005 ATDST 01/03/03 03:42 A45
031900 65535 AV 01/03/03 04:42 A45