On Jan 4, 7:46 am, Totti <saliba.toufic.geo
...@gmail.com> wrote:
> 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
I suspect that it is the inclusion of "purch.Prod_Code" in the GROUP
BY clause that is causing the problem.
This:
(case when purch.Prod_Code = 'SP-20.2' then sum(purch.qty) else 0 end)
as "SP-20.2",
May be rewritten as:
SUM(DECODE(PURCH.PROD_CODE,'SP-20.2',PURCH.QTY,0)) AS "SP-20.2"
By making similar changes to the other columns that are returned, you
would be able to remove "purch.Prod_Code" from the GROUP BY clause.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
