Re: huge returned number problems
Date: Tue, 15 Jan 2008 07:17:16 -0800 (PST)
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 Received on Tue Jan 15 2008 - 09:17:16 CST