Re: huge returned number problems

On Jan 15, 10:17 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Jan 15, 9:06 am, Totti <saliba.toufic.geo..._at_gmail.com> wrote:
>
>
>
>
>
> > hi all, i want to Show total purchases by supplier in 2006.
> > and i want to nclude only those suppliers from whom we made purchases
> > of more than 2,000,000 during the first 6 months of 2006.
> > so i did the following:
>
> > select purpmt.suppl_code as "Supplier",
> > sum(purch.qty * purch.unit_price) "Total Purchases"
> > from purch, purpmt , purinv
> > having to_char(purinv.inv_date,'mm') < '07'
> > and to_char(purinv.inv_date,'yy') = '06'
> > and (purch.qty * purch.unit_price) > 2000000
> > and purinv.suppl_code = purpmt.suppl_code
> > group by purpmt.suppl_code
> > order by purpmt.suppl_code
>
> > but this is giving me huge number with +E12 and things imaginary, is
> > there any wrong logic in my query or what would the problem be
>
> > help appreciated
>
> The HAVING clause is designed as a 'where' alternative for aggregate
> values.  Possibly you should change this to read as follows:
>
>  select purpmt.suppl_code as "Supplier",
>  sum(purch.qty * purch.unit_price) "Total Purchases"
>  from purch, purpmt , purinv
>  where to_char(purinv.inv_date,'mm') < '07'
>  and to_char(purinv.inv_date,'yy') = '06'
>  and (purch.qty * purch.unit_price) > 2000000
>  and purinv.suppl_code = purpmt.suppl_code
>  group by purpmt.suppl_code
>  order by purpmt.suppl_code;
>
> I expect you'll see different, and more accurate, results.
>
> David Fitzjarrell

The problem is that the purch table is not being joined to any other table, so for every supplier you're seeing the total of all purchases in the database. Received on Tue Jan 15 2008 - 11:21:33 CST