[thelist] Query Quandry

[Joshua Olson]

----- Original Message -----
From: "Jay Blanchard" <jay.blanchard at niicommunications.com>
Sent: Thursday, April 04, 2002 2:17 PM
Subject: [thelist] Query Quandry


> I am performing the following query;
>
> SELECT  sot.BillingNumber,
>         sot.BillDate,
>         sot.SOCompletionDate,
>         sot.SONumber,
>         sot.PONumber,
>         SUM(son.USONetChange) AS NetChange,
>         SUM(son.USOFractionalAmount) AS FractionalAmount,
>         SUM(son.USOOneTimeAmount) AS OneTimeAmount,
>         SUM(son.USOBilledAmount) AS BilledAmount
> FROM    tblOCCSvcOrdTransID00 sot,
>         tblOCCSvcOrdTransNetEffect son
> WHERE   son.BillDate = '2002-03-05'
> AND     son.BillingNumber = '7105575015'
> AND     sot.BillDate = son.Billdate
> AND     sot.BillingNumber = son.BillingNumber
> GROUP BY son.SONumber;

Jay,

Just a quick reminder, you cannot return row level detail in a summary query
that uses GROUP BY.  Returning row level details will cause all sorts of
nasty results.  In your query, all the columns in the select statement
without Aggregate functions (except SONumber, which is specified in the
GROUP BY clause and is therefore okay to select without an aggregate
function) may cause the system to return unexpected information.

Is there a 1-1 correspondence between son and sot?  Having multiple records
for sot for each son (related using the BillingNumber) are going to further
complicate this query.

If you rework the query, keeping in mind the fact that GROUP BY produces a
summary of rows and the limitation mentioned above, it should all begin to
be clear how to fix the query.

HTH,

-joshua

see tip on this page:
http://lists.evolt.org/index.cfm/a/harvest/b/week/c/Week-of-Mon-20011001.htm
l