Re: Query

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Mon, 15 Sep 2008 18:09:37 +0200
Message-ID: <48ce88c7$0$201$e4fe514c@news.xs4all.nl>

"Mtek" <mtek_at_mtekusa.com> schreef in bericht news:0eb27f6d-e7b4-4c62-8c54-1c1dfdff77fd_at_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......

---

Don't put your column aliases in single quotes .... 'Product1' etc

Shakespeare Received on Mon Sep 15 2008 - 11:09:37 CDT