Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to use the WITH clause

Re: How to use the WITH clause

From: Kin Ng <kin_ng5_at_yahoo.com>
Date: 12 Jul 2003 12:09:36 -0700
Message-ID: <d5b3f600.0307121109.45ec76b5@posting.google.com>


"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:<cdnPa.5029$5A.2343_at_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


It works using 9i client. Thanks Received on Sat Jul 12 2003 - 14:09:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US