Oracle 10g, Ranking the records based on Amount

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Re: Oracle 10g, Ranking the records based on Amount

Postby Naveed Ul islam » Fri Jul 26, 2013 7:40 am

Tim; Please clear a point:
If there are multiple joins in ON clause then do i need to add all columns in Partition by clause?
Naveed Ul islam
Member
 
Posts: 10
Joined: Wed Jul 24, 2013 9:58 am

Re: Oracle 10g, Ranking the records based on Amount

Postby Naveed Ul islam » Fri Jul 26, 2013 10:22 am

Error..ORA-30483 Window function not allowed here. Pl suggest the solution.

UPDATE RPT_CUST_SEGMENT_LOOKUP R
SET R.ARPU_DESC =

CASE
WHEN RANKING_FLAG = 5 THEN
'>= Rs ' || ARPU

WHEN RANKING_FLAG = 4 THEN
'>= Rs ' || ARPU || ' & < Rs ' || LAG(ARPU, 1, 0) ----exception on Lag

OVER(ORDER BY ARPU DESC)

WHEN RANKING_FLAG = 3 THEN
'>= Rs ' || ARPU || ' & < Rs ' || LAG(ARPU, 1, 0)
OVER(ORDER BY ARPU DESC)

WHEN RANKING_FLAG = 2 THEN
'>= Rs ' || ARPU || ' & < Rs ' || LAG(ARPU, 1, 0)
OVER(ORDER BY ARPU DESC)

WHEN RANKING_FLAG = 1 THEN
'< Rs ' || ARPU

END ARPU_DESC
WHERE BILLING_MONTH = '201306';
Naveed Ul islam
Member
 
Posts: 10
Joined: Wed Jul 24, 2013 9:58 am

Re: Oracle 10g, Ranking the records based on Amount

Postby Tim... » Fri Jul 26, 2013 2:31 pm

Hi.

Analytic functions run against a query, which is why I suggested using a MERGE with an UPDATE clause. This allows you to easily use a query as the source of the data, making the use of analytic functions possible.

You are trying to use them out of the context, which is why they fail.

If you look up the error message, you will see exactly why this will not work.

"ORA-30483: window functions are not allowed here
Cause: Window functions are allowed only in the SELECT list of a query. And, window function cannot be an argument to another window or group function.
Action: None"


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Previous

Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 7 guests