Re: Query

From: Mtek <mtek_at_mtekusa.com>
Date: Mon, 15 Sep 2008 08:23:14 -0700 (PDT)
Message-ID: <0eb27f6d-e7b4-4c62-8c54-1c1dfdff77fd@y38g2000hsy.googlegroups.com>

On Sep 15, 8:41 am, Carlos <miotromailcar..._at_netscape.net> wrote:
> On 15 sep, 15:31, Mtek <m..._at_mtekusa.com> wrote:
>
>
>
>
>
> > Hi,
>
> > I was wondering if I can display data like this:
>
> > Original Data
> > 01-JUN-08 Type1           Add           4308
> > 01-JUN-08 Type1           Delete        4687
> > 01-OCT-08 Type2           Delete        5545
> > 01-OCT-08 Type2           Add          11801
>
> > New Data
> > 01-JUN-08 Type1      Add    4308   Delete     4687
> > 01-OCT-08 Type2      Add   11801   Delete     5545
>
> > I want all related records on the same line......
>
> > Much thanks
>
> Google for 'PIVOT TABLE'
>
> HTH
>
> Cheers.
>
> Carlos.- Hide quoted text -
>
> - Show quoted text -

Ok, I find this example on Tom Kyte's page:

select job,

max( decode( deptno, 10, cnt, null ) ) dept_10,
max( decode( deptno, 20, cnt, null ) ) dept_20,
max( decode( deptno, 30, cnt, null ) ) dept_30,
max( decode( deptno, 40, cnt, null ) ) dept_40
from ( select job, deptno, count(*) cnt
         from emp
        group by job, deptno )

group by job;

Mine is almost identical, but I get an error:

select TRUNC(action_date,'MM'),

  max( decode( email_product_id, 'PPM', cnt, null ) ) 'Product1',
  max( decode( email_product_id, 'HL',  cnt, null ) ) 'Product2',
  max( decode( email_product_id, 'ZI',  cnt, null ) ) 'Product3',
  max( decode( email_product_id, 'PP',  cnt, null ) ) 'Product4',
  max( decode( email_product_id, 'PP1', cnt, null ) ) 'Product5'
from ( select TRUNC(action_date,'MM'), email_product_id, count(*) cnt
         from email_product_hist
        group by email_product_id )

group by email_product_id;

ERROR at line 2:
ORA-00923: FROM keyword not found where expected

I cannot get this one...... Received on Mon Sep 15 2008 - 10:23:14 CDT