Home » SQL & PL/SQL » SQL & PL/SQL » with <dummy_name> as ..
with <dummy_name> as .. [message #300711] Sun, 17 February 2008 22:42 Go to next message
kidoos
Messages: 11
Registered: June 2007
Junior Member
I have seen some a query witten
as
WITH t AS
	 (SELECT ename,deptno FROM scott.emp), t2 AS 
	 (SELECT deptno FROM t WHERE ROWNUM <2)
	 SELECT deptno FROM t2


can anyone please explain these type of queries.
and wat is its pros and cons ?
can this be used in plsql curosr?

Re: with <dummy_name> as .. [message #300749 is a reply to message #300711] Mon, 18 February 2008 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SELECT.

Regards
Michel
Re: with <dummy_name> as .. [message #300791 is a reply to message #300749] Mon, 18 February 2008 02:36 Go to previous messageGo to next message
kidoos
Messages: 11
Registered: June 2007
Junior Member
if i write a query as follows , it is showing

ORA-00907: missing RIGHT parenthesis

WITH t AS
	 ((SELECT ename,deptno FROM scott.emp ORDER BY ename) 
	  UNION ALL (SELECT ename,deptno FROM scott.emp  ORDER BY ename) ), t2 AS 
	 (SELECT deptno FROM t WHERE ROWNUM <2)
	 SELECT deptno FROM t2 
Re: with <dummy_name> as .. [message #300794 is a reply to message #300791] Mon, 18 February 2008 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste execution.
In addition, use SQL Formatter.

Regards
Michel
Re: with <dummy_name> as .. [message #300795 is a reply to message #300791] Mon, 18 February 2008 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Clue: your error has no relation with WITH, read SELECT syntax and try to execute each part.

Regards
Michel
Re: with <dummy_name> as .. [message #300829 is a reply to message #300711] Mon, 18 February 2008 03:36 Go to previous messageGo to next message
kidoos
Messages: 11
Registered: June 2007
Junior Member
we use toad here .will definitely use sql plus next time.

if i remove order by clause
it works . does order by has any restriction in this
case ?

[Updated on: Mon, 18 February 2008 03:37]

Report message to a moderator

Re: with <dummy_name> as .. [message #300840 is a reply to message #300829] Mon, 18 February 2008 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again read SELECT page and you'll know the answer.
Where can you put an ORDER BY clause?

Regards
Michel
Re: with <dummy_name> as .. [message #300899 is a reply to message #300829] Mon, 18 February 2008 10:37 Go to previous message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
kidoos wrote on Mon, 18 February 2008 01:36
we use toad here .will definitely use sql plus next time.

if i remove order by clause
it works . does order by has any restriction in this
case ?


Check out the section on set operators (like union all), scroll down to restrictions, and see what it says about order by:


http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries004.htm#i2054381
Previous Topic: selecting result of row that occurs more than 1.
Next Topic: IOT tables.
Goto Forum:
  


Current Time: Mon Dec 05 11:06:41 CST 2016

Total time taken to generate the page: 0.10246 seconds