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

 

[SantaMufasa]

WSam,

I shall hazard a guess that the reason no one has responded to your thread (here and in the other fora) is because none of us is willing to mentally process all your code.

To help you resolve your problem, however, I'll restate a hard-and-fast rule of relational result sets: "If you receive too many, or too few, rows in a result set, it is the fault of the WHERE clause."

In your case, you do not have a true Cartesian product. A true Cartesian product results from matching every row in one set with every row in another set. I do not believe that is happening in your case. You are simply receiving more rows in your result set than you want. I guarantee that your problem (or too many resulting rows) is a faulty WHERE clause. You, however, must be the detective to isolate the "WHERE" problem since we do not have access to your data (leaving the data to our imaginations) and your SELECT is more complex than we Tipsters are willing to process "imaginary" data mentally.

Let us know what you discover.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:29 (05Mar04) UTC (aka "GMT" and "Zulu"), 09:29 (05Mar04) Mountain Time)