Re: Creating View with WITH clause

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 Received on Wed Aug 02 2006 - 05:38:44 CDT