Home » SQL & PL/SQL » SQL & PL/SQL » WITH Clause (oracle 9i)
WITH Clause [message #302773] Wed, 27 February 2008 00:24 Go to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
This query return error,

SQL> with data as (select * from emp) select * from data;

unknown command beginning "with data ..." - rest of line ignored.

What's problem? Can we use WITH Clause? Any version Problem?


Thanks

Mano
Re: WITH Clause [message #302777 is a reply to message #302773] Wed, 27 February 2008 00:28 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
available in 10g or later.


regards,
Re: WITH Clause [message #302791 is a reply to message #302777] Wed, 27 February 2008 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Correction, available from 9i.

Regards
Michel
Re: WITH Clause [message #302792 is a reply to message #302773] Wed, 27 February 2008 00:48 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Thanks Michel,

But i am using Oracle 9i only... its return error... any configuration?

Thanks
Mano

Re: WITH Clause [message #302794 is a reply to message #302792] Wed, 27 February 2008 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Which 9i?
What is your client version? What is you db version? With 4 decimals.
Always copy and paste your session.

Regards
Michel
Re: WITH Clause [message #302795 is a reply to message #302792] Wed, 27 February 2008 00:56 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
sorry @Mano.should have checked before posting.Thanks Michel for correcting.

WITH CLAUSE


regards,


Re: WITH Clause [message #302797 is a reply to message #302792] Wed, 27 February 2008 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> with data as (select * from dual) select * from data;
D
-
X

1 row selected.

SQL> @v

Version Oracle : 9.0.1.2.0

Regards
Michel
Re: WITH Clause [message #302830 is a reply to message #302773] Wed, 27 February 2008 02:14 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for Linux: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production
Re: WITH Clause [message #302835 is a reply to message #302830] Wed, 27 February 2008 02:23 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
$ sqlplus ro/ro@demodb

Connecting: ro/ro@demodb

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Feb 27 09:20:48 2008

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> with data as ( select sysdate from dual ) select * from data ;

SP2-0734: unknown command beginning "with data ..." - rest of line ignored.


My database is 10g, but my Client is 817.

Which Client-version are you using?

Re: WITH Clause [message #302838 is a reply to message #302773] Wed, 27 February 2008 02:32 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
SQL*Plus: Release 8.0.6.0.0 - Production on Wed Feb 27 14:02:33 2008

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Re: WITH Clause [message #302839 is a reply to message #302773] Wed, 27 February 2008 02:33 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
if i need means what i should do?
Re: WITH Clause [message #302840 is a reply to message #302839] Wed, 27 February 2008 02:35 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
What do YOU think you should do?
Re: WITH Clause [message #302842 is a reply to message #302773] Wed, 27 February 2008 02:40 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
If client version is below 9i means we can't use WITH clause right?
Re: WITH Clause [message #302844 is a reply to message #302842] Wed, 27 February 2008 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If SQL*Plus is less than 9i, you can't use WITH.
But client can be less than 9i if you use another tool.

Regards
Michel
Re: WITH Clause [message #302846 is a reply to message #302773] Wed, 27 February 2008 02:53 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Michel please tell me

WITH is SQL *PLUS command or SQL Command?

Re: WITH Clause [message #302851 is a reply to message #302846] Wed, 27 February 2008 03:09 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Since sqlplus has a set of its own commands, it first tries to interpret what you enter.
This means, that it must be able to recognize SQL as being SQL.
Since WITH ... was not valid SQL in 8.0.6, sqlplus does not recognize your query as being SQL.
So: upgrade your client.
Re: WITH Clause [message #302853 is a reply to message #302846] Wed, 27 February 2008 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
As Frank said, SQL*PLus contains a command interpreter to know if what you enter is:
- a SQL*Plus command
- a SQL statement
If it is not one of these, it returns an error.
And WITH is neither a SQL*Plus command nor a SQL statement for 8.x SQL*Plus.

Regards
Michel
Should I stay or should I go? [message #302930 is a reply to message #302839] Wed, 27 February 2008 06:45 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
spmano1983 wrote on Wed, 27 February 2008 09:33
what i should do?

Instead of
with data as (select * from emp) select * from data;


Something like this:
create view data as select * From emp;

select * from data;
Re: Should I stay or should I go? [message #302953 is a reply to message #302930] Wed, 27 February 2008 07:25 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Littlefoot wrote on Wed, 27 February 2008 13:45
spmano1983 wrote on Wed, 27 February 2008 09:33
what i should do?


Something like this:
....

No!
Upgrade your client along with your server!
Re: WITH Clause [message #302962 is a reply to message #302838] Wed, 27 February 2008 07:50 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
spmano1983 wrote on Wed, 27 February 2008 03:32
SQL*Plus: Release 8.0.6.0.0 - Production on Wed Feb 27 14:02:33 2008

(c) Copyright 1999 Oracle Corporation.  All rights reserved.



Just to let you know, this version isn't even 8i, it's 8.0, which was desupported on 30 Sep 2001.
Previous Topic: Getting the avarage time pl/sql
Next Topic: String too long (Concatenation)
Goto Forum:
  


Current Time: Fri Feb 07 08:43:04 CST 2025