Home » SQL & PL/SQL » SQL & PL/SQL » Missing right paren... subquery trouble (Oracle 11g)
Missing right paren... subquery trouble [message #612355] Wed, 16 April 2014 08:54 Go to next message
pagoo
Messages: 1
Registered: April 2014
Junior Member
I'm pulling my hair out here because I can't figure out why it is telling me I'm missing a right parenthesis.

WITH mydata AS (
SELECT DBMS_LOB.substr(summary, 4000) test
    FROM ((REGEXP_REPLACE(UPPER(TEST), ' ', '#') test)
    FROM (SELECT REGEXP_REPLACE (replace(summary,'-','.'), '[' ||  REGEXP_REPLACE (replace(summary,'-','.') || '!', '[^[:punct:]]')  || ']') test 
    FROM my_table))
),
splitted_words as 
( 
SELECT REGEXP_SUBSTR(TEST,'[^#]+', 1, level) AS word 
FROM mydata 
CONNECT BY level <= LENGTH(regexp_replace(TEST,'[^#]')) + 1 
AND PRIOR ROWID = ROWID
AND PRIOR sys_guid() IS NOT NULL 
)
SELECT word, 
COUNT(1) 
FROM splitted_words
GROUP BY word
ORDER BY COUNT(1) DESC;


specifically at the line
FROM ((REGEXP_REPLACE(UPPER(TEST), ' ', '#') test)
Re: Missing right paren... subquery trouble [message #612356 is a reply to message #612355] Wed, 16 April 2014 09:02 Go to previous messageGo to next message
BlackSwan
Messages: 22802
Registered: January 2009
Senior Member
welcome to this forum.

CONGRATULATIONS of finding, reading and following the forum's Posting Guideline.
Fewer than 1 out of 100 new posters do as you did.

This is the first time since I have been doing Oracle that I have ever seen a single SELECT statement with 3 FROM clauses.

since we don't have your tables or data, we can't run, test or debug posted code.
Re: Missing right paren... subquery trouble [message #612357 is a reply to message #612356] Wed, 16 April 2014 09:12 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
From must be immediately followed by a table/view name, a pipelined function or a select statement. That's not true for this:
FROM ((REGEXP_REPLACE(UPPER(TEST), ' ', '#') test)
Re: Missing right paren... subquery trouble [message #612359 is a reply to message #612355] Wed, 16 April 2014 09:36 Go to previous message
Michel Cadot
Messages: 59170
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please format your SQL.
If you don't know how to do it, learn it using SQL Formatter.

And it will also gives you where's the error when syntax is not valid:
((3,26) expected token:; @ IDENTIFIER INSERT BY AS TIMESTAMP COMMIT REVOKE TRUNCATE CLOSE FUNCTION PACKAGE COUNT TRIGGER CONSTRAINTS SEQUENCE TRANSACTION TRIGGERS OVERLAPS

SQL*Plus will also tell you:
SQL> WITH mydata AS (
  2  SELECT DBMS_LOB.substr(summary, 4000) test
  3      FROM ((REGEXP_REPLACE(UPPER(TEST), ' ', '#') test)
  4      FROM (SELECT REGEXP_REPLACE (replace(summary,'-','.'), '[' ||  REGEXP_REPLACE (replace(summary,'-','.') || '!', '[^[:punct:]]')  || ']') test 
  5      FROM my_table))
  6  ),
  7  splitted_words as 
  8  ( 
  9  SELECT REGEXP_SUBSTR(TEST,'[^#]+', 1, level) AS word 
 10  FROM mydata 
 11  CONNECT BY level <= LENGTH(regexp_replace(TEST,'[^#]')) + 1 
 12  AND PRIOR ROWID = ROWID
 13  AND PRIOR sys_guid() IS NOT NULL 
 14  )
 15  SELECT word, 
 16  COUNT(1) 
 17  FROM splitted_words
 18  GROUP BY word
 19  ORDER BY COUNT(1) DESC;
    FROM ((REGEXP_REPLACE(UPPER(TEST), ' ', '#') test)
                         *
ERROR at line 3:
ORA-00907: missing right parenthesis

Now Cookiemonster already told you what you have to change.
Previous Topic: query of status changed
Next Topic: Need to get max update date time last 7 days
Goto Forum:
  


Current Time: Tue Sep 23 01:33:26 CDT 2014

Total time taken to generate the page: 0.16718 seconds