Re: How to use the WITH clause

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Fri, 11 Jul 2003 00:07:36 GMT
Message-ID: <cdnPa.5029$5A.2343@news02.roc.ny>

"Kin Ng" <kin_ng5_at_yahoo.com> wrote in message news:d5b3f600.0307100841.13da0c31_at_posting.google.com...

> "Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:<sI4Pa.1845$C64.1157_at_news02.roc.ny>...

> > "Kin Ng" <kin_ng5_at_yahoo.com> wrote in message news:d5b3f600.0307091714.7da04ed5_at_posting.google.com...
> > > Can someone tell me what's wrong with my use of the WITH clause?
> > >
> > > with summary as
> > > (
> > > select brand_code from tblmasters where created_by < sysdate
> > > )
> > > select brand_code from summary
> > >
> > > I kept getting "SQL statement doesn't return rows" error.
> > >
> > > Thanks
> >
> > nothing wrong with the clause. Though you seem to be using it unnecessarily.
> > What is the *exact* error you are getting.
> > btw: are you getting "no rows selected" .. which is not an error.
> >
> > Anurag

>
> Anurag,
>
> The statement is a test to see if WITH will work.  I used Toad and I
> got that "SQL statement doesn't return rows" error from a Pop Up
> dialog box.  If I used SQL Plus, I got this:
>
> SQL> WITH summary AS
> SP2-0734: unknown command beginning "WITH summa..." - rest of line
> ignored.
> SQL>
>
> Here's my SQL Plus and DB version Info:
>
> SQL*Plus: Release 8.1.7.0.0 - Production on Wed Jul 9 17:25:20 2003
>
> (c) Copyright 2000 Oracle Corporation.  All rights reserved.
>
>
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
> With the Partitioning option
> JServer Release 9.2.0.3.0 - Production

Not sure . My guess:
* Try using a 9i client.
* Check the compatible parameter .. it should be set to at least 9.0.0.1

Anurag Received on Thu Jul 10 2003 - 19:07:36 CDT