Running Totals in Oracle 8i (analytic functions)

[mirogak]

Folks,

I researched how to do running totals in Oracle, and found that Oracle has analytical functions like OVER .

However, when I tried this simple query it keeps giving me an error message ORA-00923, that a FROM keyword is expected right somewhere around the keyword "ORDER".

Please see example,

SELECT
B.REVENUE_DIVISION,
B.YEAR,
B.MONTH,
B.GROSS_BUDGET,
SUM(GROSS_BUDGET)
OVER (ORDER BY B.POST_YEAR, B.MONTH) AS "RUNNING_TOTAL"
FROM
GDW_DM.BUDGET B

And I get the error message with above keyword "ORDER" highlighted.

What am i doing wrong?

Thx,
mirogak

 

[Dagon]

The syntax works OK for me. Are you sure you have the right database version. You need the Enterprise edition of Oracle.

 

[karluk]

I don't have any 8i databases any longer to check, but my recollection is that analytic functions in Oracle 8i were only partially supported. They were available in PL/SQL but not in SQL. You are probably running into this issue. If you can't run your query on a 9i or 10g database, you have two work-arounds available:

1. Write a simple pl/sql block which executes your query.
2. Create a view with your select statement (including the analytic function). Then select from the view rather than the underlying table.

 

[karluk]

Nope, I had it just reversed - analytic functions in 8i worked in SQL but not PL/SQL. See thread186-372531.

Since your query doesn't look like PL/SQL, I'm guessing that Dagon is right. Perhaps you are using an older version of Oracle in which analytic functions weren't supported.

 

[mirogak]

I am telling you guys, its weird.

I am running Oracle 8i through Toad version 8.5.3.2

could Toad be doing this?

 

[mirogak]

This is the exact query

SELECT
B.REVENUE_DIVISION,
B.POST_YEAR,
B.POST_PERIOD,
B.GROSS_BUDGET,
SUM(GROSS_BUDGET)
OVER (ORDER BY B.POST_YEAR, B.POST_PERIOD) AS "RUNNING_TOTAL"
FROM
GDW_DM.BUDGET B


thx
mirogak

 

[mirogak]

Dagon and Karluk,

Thx for your replies ...

Dagon, it was the version.

Although analytical functions were supported in 8i(version 8.1.6 or something like that) and our grandpa database is 8.1.5 (sorry I forget the exact numbers)

Point being, we have the version just before they released analytical function capability.

thanks,

Karluk,
I am not bad in SQL and never done any PL/SQL. Can you think of another (smarter, shorter) way of calculating running totals in plain SQL?

L8R,
mirogak