Home » SQL & PL/SQL » SQL & PL/SQL » Whats wrong with the following code?
Whats wrong with the following code? [message #328798] Sun, 22 June 2008 21:41 Go to next message
renu4dwh
Messages: 24
Registered: April 2008
Location: Pune
Junior Member
I have a table T1 with two columns a and b.

This piece of code is supposed to remove redundant duplicate rows from table T1.

Whats wrong with the following code? It says

ERROR at line 2:
ORA-00907: missing right parenthesis


DELETE FROM T1
WHERE ROWID IN (SELECT P2.ROWID FROM T1 AS P2,
(SELECT P3.a,P3.b,MAX(P3.ROWID) AS max_rowid FROM T1 AS P3
GROUP BY P3.a,P3.b) AS P4
WHERE P2.ROWID <> P4.max_rowid
AND P2.a=P4.a
AND P2.b=P4.b);
Re: Whats wrong with the following code? [message #328806 is a reply to message #328798] Sun, 22 June 2008 23:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above

It very well could be a bug, but without knowing Oracle version I won't speculate.
Re: Whats wrong with the following code? [message #328807 is a reply to message #328798] Sun, 22 June 2008 23:08 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
> Whats wrong with the following code?
It does not follow the syntax of SELECT Statement, as described in Oracle documentation, found e.g. online on http://tahiti.oracle.com/.
Be aware, that Oracle does not fully support ISO standards. The differences are stated in http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/ap_standard_sql.htm#i10292. In your case, you may find interesting this part:
Quote:
Feature ID E051
Feature Basic query specification
Partial Support Oracle partially supports the following subfeatures:
E051-08, Correlation names in FROM clause (Oracle supports correlation names, but not the optional AS keyword)

Re: Whats wrong with the following code? [message #328814 is a reply to message #328798] Sun, 22 June 2008 23:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Next time use SQL Formatter to pretty format your query.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Whats wrong with the following code? [message #328824 is a reply to message #328806] Mon, 23 June 2008 00:02 Go to previous messageGo to next message
renu4dwh
Messages: 24
Registered: April 2008
Location: Pune
Junior Member
anacedent wrote on Sun, 22 June 2008 23:07
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above

It very well could be a bug, but without knowing Oracle version I won't speculate.



Well, i am confused regarding the violation of rules. I guess i didnt do any formatting of the code, if that is so, i am so sorry and i will take care of that thing.

Here is the Version information :

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
Re: Whats wrong with the following code? [message #328825 is a reply to message #328807] Mon, 23 June 2008 00:05 Go to previous messageGo to next message
renu4dwh
Messages: 24
Registered: April 2008
Location: Pune
Junior Member
flyboy wrote on Sun, 22 June 2008 23:08
> Whats wrong with the following code?
It does not follow the syntax of SELECT Statement, as described in Oracle documentation, found e.g. online on http://tahiti.oracle.com/.
Be aware, that Oracle does not fully support ISO standards. The differences are stated in http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/ap_standard_sql.htm#i10292. In your case, you may find interesting this part:
Quote:
Feature ID E051
Feature Basic query specification
Partial Support Oracle partially supports the following subfeatures:
E051-08, Correlation names in FROM clause (Oracle supports correlation names, but not the optional AS keyword)




Thank you flyboy, i was reading a book on Sql Programming and there i got this piece of Code, where the author didn't specifically mentioned which all products will support it.I thought it may work in Oracle.
Re: Whats wrong with the following code? [message #328830 is a reply to message #328825] Mon, 23 June 2008 00:21 Go to previous messageGo to next message
renu4dwh
Messages: 24
Registered: April 2008
Location: Pune
Junior Member
Can we use the same logic in Oracle and by making some changes to the syntax, we shall be able to achieve the goal?
Re: Whats wrong with the following code? [message #328833 is a reply to message #328798] Mon, 23 June 2008 00:43 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
> Can we use the same logic in Oracle and by making some changes to the syntax, we shall be able to achieve the goal?

Yes, just remove the AS keyword before all table aliases:
SQL> select dummy as c1 from dual as t1;
select dummy as c1 from dual as t1
                             *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> select dummy as c1 from dual t1;

C
-
X

SQL> 
Re: Whats wrong with the following code? [message #328841 is a reply to message #328833] Mon, 23 June 2008 01:08 Go to previous message
renu4dwh
Messages: 24
Registered: April 2008
Location: Pune
Junior Member
flyboy wrote on Mon, 23 June 2008 00:43
> Can we use the same logic in Oracle and by making some changes to the syntax, we shall be able to achieve the goal?

Yes, just remove the AS keyword before all table aliases:
SQL> select dummy as c1 from dual as t1;
select dummy as c1 from dual as t1
                             *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> select dummy as c1 from dual t1;

C
-
X

SQL> 



Thank you flyboy. Now the query is working perfectly.
Previous Topic: Adding row values
Next Topic: How to select the database objects
Goto Forum:
  


Current Time: Sun Nov 10 05:58:12 CST 2024