Re: would you please check this for me?

On Jan 3, 12:43 pm, Totti <saliba.toufic.geo..._at_gmail.com> wrote:
> hi all, i ve been struggeling with this function and i can not see
> what is the problem.
> i am using it for getting the following result:
> ***************************************************************************­*******************************
> 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
>    05 1999          0        162         87
>    06 1999        413          0         90
>    07 1999          0          0          0
>
> ***************************************************************************­*******************************
> and 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(nvl(purch.qty,0)) else
> sum(0) end SP-20.2,
> case when purch.Prod_Code = 'SP-20.1' then sum(nvl(purch.qty,0)) else
> sum(0) end SP-20.1,
> case when purch.Prod_Code = 'SP-20.6' then sum(nvl(purch.qty,0)) else
> sum(0)end 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')
> MONTH ,TO_CHAR(purinv.Inv_Date,'YYYY') ,purch.Prod_Code
> order by TO_CHAR(purinv.Inv_Date,'mm')
> MONTH ,TO_CHAR(purinv.Inv_Date,'YYYY')
>
> Thank you for helping.

you have the alias MONTH in the group by and order by clauses

you have parens around what appears to be 3 separate columns, that should be dropped.

you are using reserved keywords as column names, so you should probably enclose with quotations marks (i.e. "MONTH")

shouldnt your summarizations be something like this: sum(case when purch.Prod_Code = 'SP-20.2' then nvl(purch.qty,0) else 0 end) SP-20.2 Received on Fri Jan 04 2008 - 09:29:59 CST