Re: SQL Help Needed

From: Jason Jay Weiland <archduke_at_uclink4.berkeley.edu>
Date: Mon, 02 Nov 1998 15:17:06 -0800
Message-ID: <363E3D72.315F2397@uclink4.berkeley.edu>

Hey Van,

     You're absolutly right. I didn't test the query against the possibility of multiple entries. A better solution would be to make an in-line view of each grouping:

SELECT
  Req.R1 R1, Req.S1 S1, Det.S2 S2
FROM

     (SELECT request.request R1,
             NVL(sum(draft.amount), 0) S1
        FROM request, draft
       WHERE request.request = draft.request(+)

    GROUP BY request.request) Req,

     (SELECT request.request R1,
             NVL(sum(detail.quantity * detail.rate), 0) S2
        FROM request, detail
       WHERE request.request = detail.request(+)

    GROUP BY request.request) Det

WHERE Req.R1 = Det.R1
  AND S1 <> S2

...this will also pull out entries where there may be no Draft or Detail record. If you do not need details from the Request table you could also try the following:

SELECT
  Req.R1 R1, Req.S1 S1, NVL(Det.S2, 0) S2 FROM (

      SELECT request R1, sum(amount) S1
        FROM draft

    GROUP BY request) Req,

     (SELECT request R1, sum(quantity * rate) S2
        FROM detail

    GROUP BY request) Det

WHERE Req.R1 = Det.R1(+)
  AND (S1 <> S2 OR S2 IS NULL)

...the second query will pull out records where there been no record entries in Detail yet. The same could be done in instances where there are no Requests, but Details records by switching the outer join. The second would also be a bit faster.

Jay!!!

P.S> Thanks for correcting me about the initial response :)

Van Messner wrote:

> Hi Jay
>
> I tried your SQL but selecting the three columns always results in a
> cartesian product on columns two and three no matter what restrictions you
> might put in the Having clause.
>
> Van
Received on Mon Nov 02 1998 - 17:17:06 CST