Home » SQL & PL/SQL » SQL & PL/SQL » missing right paranthesis (oracle 10.1.2)
missing right paranthesis [message #343933] Thu, 28 August 2008 08:26 Go to next message
kimber
Messages: 10
Registered: August 2008
Location: USA
Junior Member
Dear Guru's
I dont knwo why i'm getting this error, any advice?

CASE WHEN DAYS IS NULL AND NON_NULL_RECORDS > (TOTAL_RECORDS)/2 THEN MAX(DAYS) OVER (PARTITION BY TO_CHAR(POLLDATE,'yy"Q"q') quarter) ELSE DAYS END DAYS, 



The error is ORA-00907: missing right parenthesis

Thank you
Re: missing right paranthesis [message #343942 is a reply to message #343933] Thu, 28 August 2008 08:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
OVER (PARTITION BY TO_CHAR(POLLDATE,'yy"Q"q') quarter)


You've included the text Quarter in the Over clause - it's expecting a right bracket before the test 'quarter'
Re: missing right paranthesis [message #343963 is a reply to message #343942] Thu, 28 August 2008 09:08 Go to previous messageGo to next message
kimber
Messages: 10
Registered: August 2008
Location: USA
Junior Member
I'm sorry but didnt get your point. When i included a right paranthesis its giving me a new error :
ORA-00905: missing keyword when i used

CASE WHEN DAYS IS NULL AND NON_NULL_RECORDS > (TOTAL_RECORDS)/2 THEN MAX(DAYS) OVER (PARTITION BY TO_CHAR(SELECTION_DATE,'yy"Q"q'))quarter) ELSE DAYS END DAYS
,
JRowbottom wrote on Thu, 28 August 2008 08:43
OVER (PARTITION BY TO_CHAR(POLLDATE,'yy"Q"q') quarter)


You've included the text Quarter in the Over clause - it's expecting a right bracket before the test 'quarter'

Re: missing right paranthesis [message #343990 is a reply to message #343963] Thu, 28 August 2008 09:42 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
) quarter)

Quote:

expecting a right bracket before the test 'quarter'


Jrowbottom has already pointed out.Did you understand what does he mean?


Regards,
Oli

[Updated on: Thu, 28 August 2008 09:45]

Report message to a moderator

Re: missing right paranthesis [message #343994 is a reply to message #343933] Thu, 28 August 2008 09:48 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
In fact, using column alias in query_partition_clause (PARTITION BY part) of analytic function is not only useless, it is also not allowed. Check it in the documentation, found e.g. online on http://tahiti.oracle.com/.

SQL Reference
Chapter 7 Functions
SQL Functions
Analytic functions

Or, if QUARTER is another column, you shall separate it from first expression by a comma.
Re: missing right paranthesis [message #343998 is a reply to message #343994] Thu, 28 August 2008 09:53 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
select sal
from (
select row_number( ) over (order by sal) as rn,
sal
from emp
) x
where rn between 1 and 5



It helps for query like this.

Regards,
Oli

[Updated on: Thu, 28 August 2008 09:58]

Report message to a moderator

Re: missing right paranthesis [message #344001 is a reply to message #343963] Thu, 28 August 2008 09:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Replace
OVER (PARTITION BY TO_CHAR(POLLDATE,'yy"Q"q') quarter)
with
OVER (PARTITION BY TO_CHAR(POLLDATE,'yy"Q"q'))
Re: missing right paranthesis [message #344078 is a reply to message #344001] Thu, 28 August 2008 13:25 Go to previous message
kimber
Messages: 10
Registered: August 2008
Location: USA
Junior Member
Thank you very much
JRowbottom wrote on Thu, 28 August 2008 09:59
Replace
OVER (PARTITION BY TO_CHAR(POLLDATE,'yy"Q"q') quarter)
with
OVER (PARTITION BY TO_CHAR(POLLDATE,'yy"Q"q'))


Previous Topic: Is it safe to use ROWID for update statements
Next Topic: Handling an Error Occurred in Declaration Block of PL/SQL
Goto Forum:
  


Current Time: Fri Dec 09 02:04:13 CST 2016

Total time taken to generate the page: 0.16255 seconds