CODEWITHSUNDEEP
oracle-database
Jonas
Jonas

Reputation: 784

Oracle SQL formatting

I Have a query to get bill data, grouped by the calendar year and the week of the year. I want to have the evolution of bills in total. So not only the number of bills of 1 week but from the beginning until the week. I have following query 

SELECT DD.CAL_YEAR, DD.WEEK_OF_YEAR AS "Date by week", SUM(DISTINCT FAB.ID) OVER ( ORDER BY DD.CAL_YEAR, DD.WEEK_OF_YEAR ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS "Number of bills"
    FROM BILLS_TABLE FAB
    JOIN DIM_DATE DD ON FAB.BALANCE_DATE_ID = DD.ID
    GROUP BY  DD.CAL_YEAR,DD.WEEK_OF_YEAR;

But when I execute this query I get following exception:

Error: ORA-30487: ORDER BY not allowed here

SQLState: 99999 ErrorCode: 30487

The order by clause is needed for the OVER function, so what is wrong?

Upvotes: 2

Views: 79

Answers (1)

sstan
sstan

Reputation: 36473

The problem is really the use of distinct inside sum.

Because you have the order by clause, that means you are doing a cumulative sum. Performing a distinct in that case doesn't really make any sense. That's what the error is actually trying to tell you.

The documentation for the SUM analytic function mentions this:

If you specify DISTINCT, then you can specify only the query_partition_clause of the analytic_clause. The order_by_clause and windowing_clause are not allowed.

Upvotes: 4

Related Questions