window function error [message #344365] |
Fri, 29 August 2008 09:36  |
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   |
ThomasG
Messages: 3212 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   |
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 #344382 is a reply to message #344379] |
Fri, 29 August 2008 10:08   |
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?
|
|
|
|
|
|