formula help please

From: Totti <>
Date: Sat, 5 Jan 2008 14:46:35 -0800 (PST)
Message-ID: <>

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'),
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

Original text of this message