From: Totti <saliba.toufic.george_at_gmail.com>
Date: Fri, 4 Jan 2008 04:46:07 -0800 (PST)

hi all i have the following formula, it is giving me 70% of the result i want would you please check where the mistake might be?

the formula is :

SELECT TO_CHAR(purinv.Inv_Date,'mm')
MONTH ,TO_CHAR(purinv.Inv_Date,'YYYY') YEAR,
(case when purch.Prod_Code = 'SP-20.2' then sum(purch.qty) else 0 end)
as "SP-20.2",
(case when purch.Prod_Code = 'SP-20.1' then sum(purch.qty) else 0 end)
as "SP-20.1",
(case when purch.Prod_Code = 'SP-20.6' then sum(purch.qty) else 0end)
as "SP-20.6"
from purinv, purch
where purinv.Code = purch.Inv_Code and purch.Prod_Code in ('SP-20.2', 'SP-20.1', 'SP-20.6')
group by
TO_CHAR(purinv.Inv_Date,'mm') ,TO_CHAR(purinv.Inv_Date,'YYYY') ,purch.Prod_Code order by
TO_CHAR(purinv.Inv_Date,'mm') ,TO_CHAR(purinv.Inv_Date,'YYYY')

the result i am getting:

MO YEAR SP-20.2 SP-20.1 SP-20.6 -- ---- ---------- ---------- ----------

```01 1998          0        140          0
01 1998        400          0          0
01 1998          0          0         80
01 1999          0          0         89
02 1999          0        163          0
02 1999        456          0          0
02 1999          0          0         86
03 1998          0          0         99
03 1999          0          0         81
04 1998          0        163          0
*********************************************************************************************************
```
the result i want :
is to gather all 1998 for sp.20.2 lets say in one, so on so forth , what shoul be changed in the formula please?

the result i want is like such:
first everything in 1998, then 1999

Month Year SP-20.2 SP-20.1 SP-20.6 ----- ---- ---------- ---------- ----------

```   01 1998        400        140         80
02 1998          0          0          0
03 1998          0          0         99
04 1998          0        163          0
05 1998        420          0         90
06 1998          0          0          0
07 1998          0        155         88
08 1998          0        151         85
09 1998        481        145         81
10 1998          0          0          0
11 1998          0          0        110
12 1998          0          0          0
01 1999          0          0         89
02 1999        456        163         86
03 1999          0          0         81
04 1999          0          0          0

```

any help appreciated, thank you all Received on Fri Jan 04 2008 - 06:46:07 CST

Original text of this message