Re: Creating View with WITH clause

From: <r13l24r2_at_gmail.com>
Date: 2 Aug 2006 06:55:31 -0700
Message-ID: <1154526931.568344.305160@m79g2000cwm.googlegroups.com>

Gints Plivna wrote:
> r13l24r2_at_gmail.com wrote:
> > Hi all,
> >
> > I'd like to create a view based on some code that I was helped out with
> > yesterday, and I'm having some trouble with it. What I have so far is
> > (with some complexity removed and names changed to protect the
> > innocent):
> >
> > with table_flattener as (
> > select j1.batch_id, b.batch_name,
> > ...
> > from jobs.action j1, jobs.action j2, jobs.batch b
> > where j2.batch_id (+)= j1.batch_id and
> > b.batch_id=j1.batch_id )
> > select a.batch_id, max(a.sent) sent, a.batch_name, max(a.received)
> > received
> > from table_flattener a
> > group by a.batch_id, a.batch_name
> > order by 1
> >
> >
> > what I'd like to do is create a view of this exact data. Of course:
> >
> > create view myview
> > with table_flattener as (
> > etc
> >
> > doesn't work. Am I missng a really obvious way to do this?
> >
> > Thanks so much in advance!
> > m

> It seems you have omited AS
> at least following works for me:
> create view mydual as with a as (select * from dual) select * from a

> Gints Plivna
> http://www.gplivna.eu

Thanks Gints .. somehow I couldn't see the forest for the trees :) I appreciate your help.

Best,
m Received on Wed Aug 02 2006 - 08:55:31 CDT