formula help please
Date: Sat, 5 Jan 2008 14:46:35 -0800 (PST)
Message-ID: <b5c14cbf-2e80-4862-83eb-2a5d055e8db3@s19g2000prg.googlegroups.com>
hi all , i want to take out of the data (more than one table) the
total purchases and the total sales, put each in a column near to each
others and group the rows by months and years, so i thought in pseudo
code something like this would work:
if to_char of date (MM-YY).sales = to_char of date (MM-YY).purchase
and prod.code.sal = prod.cod.purch than qty*unit_price(x) in "tot
purch" and qty*unit_price(y)in "tot sales"
from tables
where prod_cod = prod_cod
so with this logic i did this:
select TO_CHAR(purinv_1.Inv_Date,'mm') || ' - ' || TO_CHAR(purinv_1.Inv_Date,'yy') as "MONTH", sum(case when to_char(salpmt_1.pmt_date,'MM-yy') = to_char(purpmt_1.pmt_date,'MM-yy') and to_char(salpmt_1.pmt_date,'mmYY')
- to_char(purpmt_1.pmt_date,'mm-YY') then (sales_1.QTY*prod_1.UNIT_PRICE)else 0 end) as "Total Sales", sum(case when to_char(salpmt_1.pmt_date,'MM-yy') = to_char(purpmt_1.pmt_date,'MM-yy') and to_char(salpmt_1.pmt_date,'mm-YY') = to_char(purpmt_1.pmt_date,'mmYY') then (purch_1.QTY*purch_1.UNIT_PRICE)else 0 end) as "Total Purchase"
from purinv_1, sales_1,purpmt_1,salpmt_1, prod_1,purch_1 where sales_1.prod_code = purch_1.prod_code group by TO_CHAR(purinv_1.Inv_Date,'mm'),TO_CHAR(purinv_1.Inv_Date,'yy')
order by TO_CHAR(purinv_1.Inv_Date,'yy')
eventhough i dont see anything wrong(**to my knowledge)
but i am having the following problem :
whenever i try to execute it the cpu goes 100% for about 5 -6 min and
than crashes what would the cause be ?
Received on Sat Jan 05 2008 - 16:46:35 CST