Home » SQL & PL/SQL » SQL & PL/SQL » Group Function with Analytic function (Oracle 10G)
Group Function with Analytic function [message #400902] Thu, 30 April 2009 05:48 Go to next message
bholeuday
Messages: 28
Registered: April 2009
Location: Talavali
Junior Member

Hi,

i have a query which should return MAX number within document_type_id, but it is giving an error. Please find the below query.

select MAX(decode(dv.document_type_id, null,ROW_NUMBER( ) OVER
(PARTITION BY dt.document_id ORDER BY ingested_dttm NULLS LAST),1))
From doc_type_version_exceptions dv,
documents dt
where dv.document_type_id(+) = dt.document_type_id
and dt.document_id = '164562'


Thanks in advance
Re: Group Function with Analytic function [message #400905 is a reply to message #400902] Thu, 30 April 2009 05:52 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are we supposed to guess what the error is?
Re: Group Function with Analytic function [message #400906 is a reply to message #400905] Thu, 30 April 2009 05:56 Go to previous messageGo to next message
bholeuday
Messages: 28
Registered: April 2009
Location: Talavali
Junior Member

Extremely sorry, i forget to mention error.

ORA-30483: window functions are not allowed here
Re: Group Function with Analytic function [message #400908 is a reply to message #400902] Thu, 30 April 2009 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session then we can see what's happening and where.

Analytic functions are the last one evaluated and so can't be used inside aggregate functions. You have to use inline views.

Regards
Michel
Re: Group Function with Analytic function [message #400927 is a reply to message #400902] Thu, 30 April 2009 07:16 Go to previous messageGo to next message
raj_betfair
Messages: 2
Registered: April 2009
Junior Member
Created table
SQL> desc tbl_test
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
ID NUMBER
IDDESC VARCHAR2(20) Y

SQL> select * from tbl_test;

ID IDDESC
---------- --------------------
7782 7782test
7890 aritest

I mimickd your analytical query and have got soemthing which you can test out ....

with q1 as (
select dt.*,
MAX(sal) OVER(PARTITION BY dt.empno ORDER BY hiredate NULLS LAST),
dv.iddesc,
row_number() OVER(PARTITION BY dt.empno ORDER BY
hiredate NULLS LAST) rn
From tbl_test dv, emp dt
where dv.id(+) = dt.empno )
select MAX(decode(sal, null,rn,1))
from q1

(you can replace the emp and test tables with your document and documentdetails table)
Re: Group Function with Analytic function [message #400931 is a reply to message #400902] Thu, 30 April 2009 07:18 Go to previous messageGo to next message
raj_betfair
Messages: 2
Registered: April 2009
Junior Member
can you paste your tables and data in thsi forum so that we can check whats wrong ?
Re: Group Function with Analytic function [message #400936 is a reply to message #400931] Thu, 30 April 2009 07:36 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Make your Query to Inline view where you calculate decode with analytical function and then take the MAX.

SELECT MAX (col1)
  FROM (SELECT decode (dv.document_type_id
                      ,NULL, row_number () OVER (PARTITION BY dt.document_id ORDER BY ingested_dttm NULLS LAST)
                      ,1
                      ) AS col1
          FROM doc_type_version_exceptions dv, documents dt
         WHERE dv.document_type_id(+) = dt.document_type_id AND dt.document_id = '164562')


From Asktom
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6349000274542

Thanks
Trivendra
Re: Group Function with Analytic function [message #400938 is a reply to message #400936] Thu, 30 April 2009 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@trivendra,

As a senior member you should know you must not post a full solution but guide OP to it and in this case you don't add anything to my post.

Also keep your lines in 80 characters.

Regards
Michel
Re: Group Function with Analytic function [message #400987 is a reply to message #400938] Thu, 30 April 2009 23:08 Go to previous message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hi Michel,

I will take care of this from now onwards.

Thanks
Trivendra.
Previous Topic: Getting more than 'anonymous block completed' from AUTONOMOUS_TRANSACTION
Next Topic: how to write a procedure for this condition
Goto Forum:
  


Current Time: Thu Dec 08 00:17:47 CST 2016

Total time taken to generate the page: 0.12247 seconds