Re: Cartesian product with totals

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 18 Jul 2001 05:10:57 -0700
Message-ID: <9j3uch0fot@drn.newsguy.com>

In article <Vsd57.22768$B5.1704171_at_monolith.news.easynet.net>, "Noah" says...
>
>Hello,
>
>I'm trying to get the sum and totals from 2 tables (well, the
>same table twice, actually) using the below SQL:
>
>SELECT dih.dih_no_post_collection
>, dih.dih_total_post_collection
>, COUNT(dir.dir_reject_id)
>, SUM(dir.dir_amount)
>, COUNT(dir2.dir_reject_id)
>, NVL(SUM(dir2.dir_amount), 0)
>FROM easygis.dl_invoice_headers dih
>, easygis.dl_invoice_rejects dir
>, easygis.dl_invoice_rejects dir2
>WHERE dih.dih_header_no = dir.dir_ih_header_no
>AND dir.dir_rejection_reason != 'No CC line produced'
>AND dih.dih_cb_trans_batch = 4942
>AND dih.dih_header_no = dir2.dir_ih_header_no
>AND dir2.dir_rejection_reason = 'No CC line produced'
>GROUP BY
> dih.dih_no_post_collection
>, dih.dih_total_post_collection
>
>It isn't producing the results I require - it's producing a
>cartesian product on the 2nd set of results. So, if there are
>100 rows for the 1st part and 50 rows for the second,
>the results are dispalyed as 100 and 5000 rows respectively.
>

cartesian products will do that -- they by definition multiply the result set like that.

>Can I get around this in a single SQL statement - at the moment
>I'm resorting to desperation and doing it as 2 separate ones.
>

it looks like you want to get the count of dir_reject_id for dih_cb_trans_batch 4942 VERSUS the count for all, and the sum for 4942 VERSUS the sum for all -- if so, a simple decode and a two table join is all you need. We use decode to either contribute the dir_reject_id or NULL to the count (count only counts non-null entries) and again to contribute either dir_amount or ZERO to the sum

If that is the query you were going for (your query wouldn't get that as you join DIH to DIR and the save only the records from DIR such that the batch = 4942 and then join that to DIR2 -- meaning only batch 4942 would be in that result as well, just multiplied), this might work:

SELECT dih.dih_no_post_collection,

       dih.dih_total_post_collection,
       COUNT( decode(dih.dih_cb_trans_batch, 4942, dir.dir_reject_id, NULL ) ),
       SUM( decode( dih.dih_cb_trans_batch, 4942, dir.dir_amount, 0 ) ),
       COUNT(dir.dir_reject_id),
       NVL(SUM(dir.dir_amount), 0)

  FROM easygis.dl_invoice_headers dih, easygis.dl_invoice_rejects dir  WHERE dih.dih_header_no = dir.dir_ih_header_no    AND dir.dir_rejection_reason != 'No CC line produced'  GROUP BY dih.dih_no_post_collection, dih.dih_total_post_collection

>Thanks for any advice,
>
>--
>Noah
>Remove ".spam.begone"
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Wed Jul 18 2001 - 07:10:57 CDT