How to use the WITH clause

#1

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

#2

"Kin Ng" <kin_ng5 (AT) yahoo (DOT) com> wrote

Quote:

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

#3

"Anurag Varma" <avdbi (AT) hotmail (DOT) com> wrote

Quote:

"Kin Ng" <kin_ng5 (AT) yahoo (DOT) com> wrote

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

#4

"Kin Ng" <kin_ng5 (AT) yahoo (DOT) com> wrote

Quote:

"Anurag Varma" <avdbi (AT) hotmail (DOT) com> wrote

"Kin Ng" <kin_ng5 (AT) yahoo (DOT) com> wrote

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

#5

"Anurag Varma" <avdbi (AT) hotmail (DOT) com> wrote

Quote:

"Kin Ng" <kin_ng5 (AT) yahoo (DOT) com> wrote

"Anurag Varma" <avdbi (AT) hotmail (DOT) com> wrote

"Kin Ng" <kin_ng5 (AT) yahoo (DOT) com> wrote

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

How to use the WITH clause

comp.databases.oracle.misc comp.databases.oracle.misc

How to use the WITH clause - 07-09-2003 , 08:14 PM

Re: How to use the WITH clause - 07-09-2003 , 10:03 PM

Re: How to use the WITH clause - 07-10-2003 , 11:41 AM

Re: How to use the WITH clause - 07-10-2003 , 07:07 PM

Re: How to use the WITH clause - 07-12-2003 , 02:09 PM