 | |
huge returned number problems
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: huge returned number problems -
01-15-2008
, 11:21 AM
|
On Jan 15, 9:06*am, Totti <saliba.toufic.geo... (AT) gmail (DOT) 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 |
table, so for every supplier you're seeing the total of all purchases
in the database.
#12
| |||
| |||
|
|
On Jan 15, 9:06*am, Totti <saliba.toufic.geo... (AT) gmail (DOT) 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 |
table, so for every supplier you're seeing the total of all purchases
in the database.
#13
| |||
| |||
|
|
On Jan 15, 9:06*am, Totti <saliba.toufic.geo... (AT) gmail (DOT) 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 |
table, so for every supplier you're seeing the total of all purchases
in the database.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.