Whats wrong with the following code? [message #328798] |
Sun, 22 June 2008 21:41 |
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 #328824 is a reply to message #328806] |
Mon, 23 June 2008 00:02 |
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 |
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 #328833 is a reply to message #328798] |
Mon, 23 June 2008 00:43 |
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 |
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.
|
|
|