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

and (purch.qty * purch.unit_price) > 2000000

would limit it to individual purchases of more than 2 million. That part needs to move from the where clause to the having clause:

having sum(purch.qty * purch.unit_price) > 2000000 Received on Tue Jan 15 2008 - 11:19:28 CST