Home » Developer & Programmer » Reports & Discoverer » Oracle Analytical Functions
Oracle Analytical Functions [message #87893] Fri, 19 October 2001 10:22 Go to next message
Erin
Messages: 17
Registered: October 2001
Junior Member
I'm trying to use the Sum function in Discoverer that uses the Range Between functionality.

SUM(n) OVER (PARTITION BY expr1 ORDER BY expr2 RANGE BETWEEN expr3 AND expr4)

I can't seem to get this to work. Does anyone have some sample code I can look at using this function. I really need to sum a dollar amount between 2 date ranges to determine a YTD value. Thanks.

----------------------------------------------------------------------
Re: Oracle Analytical Functions [message #87924 is a reply to message #87893] Tue, 30 October 2001 20:47 Go to previous messageGo to next message
Ashok kumar .D
Messages: 1
Registered: October 2001
Junior Member
I had got the same problem.but i could solve it using User defiend functions
Ex
select GetCurrentYearYTD(Year,Month),Actual,Budget,.... from my table
where perio_id between # and #

i wrote the function like the following :
function GetCurrentYearYTD(Year , Month)
returnval number
select sum(actual) into returnval from mytable
where period_year=year and period_month between 1 and Month.

since my db is larget it is giving performance issues. so iam trying to get better solution.if you feel this solves your problem ,feel free to contact me , i will give clear syntax.if you have already got better solution than this, please forward to me.

----------------------------------------------------------------------
Re: Oracle Analytical Functions [message #88299 is a reply to message #87893] Tue, 25 June 2002 06:42 Go to previous message
Alex Campbell
Messages: 1
Registered: June 2002
Junior Member
I had problems with other Analytical functions.
Previous Topic: How to shift postion of fields at runtime in reports 6i
Next Topic: Oracle Reports table of contents
Goto Forum:
  


Current Time: Thu Mar 28 05:05:36 CDT 2024