Home » SQL & PL/SQL » SQL & PL/SQL » window function error (oracle 9i)
window function error [message #344365] Fri, 29 August 2008 09:36 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi All
I'm getting the following error when i run the below query, any ideas how to over come it?

ORA-30483: window functions are not allowed here

SELECT DISTINCT ' Project Specific/Other' calc_Type
     ,TRUNC (NTPACT - Selection_Date) Days
     ,Contract_Number
     ,NTPACT
     ,SELECTION_DATE
     ,AMENDREQ
     ,RECPROP
     ,AMEND_STATUS_CODE
     ,AMENDMENT_NUMBER
     ,SELECTION_DATE AS PARAM_DATE
     ,COUNT(*) over () AS TOTAL_RECORDS
     ,COUNT(DECODE (NTPACT, NULL, 1, NULL) over () AS NULL_RECORDS
     ,COUNT(NTPACT1) over () AS NON_NULL_RECORDS
FROM CONTRACT_VW2


Thank you
Re: window function error [message #344374 is a reply to message #344365] Fri, 29 August 2008 09:58 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yep. Only use window functions where they are allowed.

If you need more information post a reproducible test case or at LEAST post your entire session. (where oracle should already have told you in which line the error is, but you didn't tell us.)
Re: window function error [message #344376 is a reply to message #344374] Fri, 29 August 2008 10:02 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
The query in BOLD is where i'm getting the error to be precise the "DECODE"

SELECT DISTINCT ' Project Specific/Other' calc_Type
     ,TRUNC (NTPACT - Selection_Date) Days
     ,Contract_Number
     ,NTPACT
     ,SELECTION_DATE
     ,AMENDREQ
     ,RECPROP
     ,AMEND_STATUS_CODE
     ,AMENDMENT_NUMBER
     ,SELECTION_DATE AS PARAM_DATE
     ,COUNT(*) over () AS TOTAL_RECORDS
     ,COUNT([COLOR=red][B]DECODE[/B][/COLOR] (NTPACT, NULL, 1, NULL) over () AS NULL_RECORDS
     ,COUNT(NTPACT1) over () AS NON_NULL_RECORDS
FROM CONTRACT_VW2
ThomasG wrote on Fri, 29 August 2008 09:58
Yep. Only use window functions where they are allowed.

If you need more information post a reproducible test case or at LEAST post your entire session. (where oracle should already have told you in which line the error is, but you didn't tell us.)

[Updated on: Fri, 29 August 2008 10:04]

Report message to a moderator

Re: window function error [message #344379 is a reply to message #344376] Fri, 29 August 2008 10:05 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Isn't there a missing closing bracket just before the over in that line?
Re: window function error [message #344382 is a reply to message #344379] Fri, 29 August 2008 10:08 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Yes, you are right sorry for the inconvenience i caused you and thank you for your valuable time, will check thoroughly next time before i post.
ThomasG wrote on Fri, 29 August 2008 10:05
Isn't there a missing closing bracket just before the over in that line?

Re: window function error [message #344385 is a reply to message #344382] Fri, 29 August 2008 10:12 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You are welcome.

Solving a problem is so much easier when you have at least a hint in which line to look. Very Happy
Re: window function error [message #344387 is a reply to message #344385] Fri, 29 August 2008 10:17 Go to previous message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
yep you are right all this time i was thinking that some thing wrong with the DECODE and didnt pay any attention to the closing bracket Sad
ThomasG wrote on Fri, 29 August 2008 10:12
You are welcome.

Solving a problem is so much easier when you have at least a hint in which line to look. Very Happy

Previous Topic: Looping through dba_objects to alter table
Next Topic: check constraint in the same table (merged 3)
Goto Forum:
  


Current Time: Thu Dec 08 17:59:05 CST 2016

Total time taken to generate the page: 0.09486 seconds