Home » Developer & Programmer » Reports & Discoverer » Dense_Rank not working in Format Trigger (Database 10g, Report Builder, Windows XP)
Dense_Rank not working in Format Trigger [message #375970] Mon, 15 December 2008 09:01 Go to next message
Messages: 48
Registered: October 2008
I have a Oracle Report for which I'm trying to add a link for downloading the report in Excel format. For that purpose, I have the following code in the format trigger of the object which displays the download link:
function B_Excel_linkFormatTrigger return boolean is

    SELECT    company_name,
     FROM (
              SELECT p.company_name as company_name,
                     mf.a_seq_num AS a_number, 
                     mf.a_start_date AS a_start_date, 
                     mfasc.description AS current_status, 
                     mfaud.city AS city,
                     mfac.comments AS comments, 
                     DENSE_RANK() OVER (PARTITION BY mf.a_seq_num ORDER BY mf.a_seq_num,mfac.comment_line_id DESC)  AS comment_rank
              FROM my_fields mf, 
                   my_fields_status_details mfasc, 
                   my_fields_asgn_act mfaa, 
                   my_fields_actors mfaud, 
                   my_fields_act_comments mfac,
                   props p
              WHERE         mf.my_fields_status_code = mfasc.my_fields_status_code
                           AND mf.a_seq_num = mfaa.a_seq_num
                           AND mfaa.my_fields_acts_seq_num = mfaud.my_fields_acts_seq_num  
                           AND mf.a_seq_num = mfac.a_seq_num
                           AND mf.p_seq_num = p.p_seq_num
                           AND p.p_seq_num <> 999999999
                           AND mf.my_fields_status_code NOT IN (199,155,160)
                           AND mfaa.main_actor_flag = 'Y'
                           AND (UPPER(mfaud.city) = UPPER(:pcity) OR UPPER(:pcity) = 'ALL')
                           AND (mfa.p_seq_num = :pp_seq_num OR :pp_seq_num = 1) 
     WHERE comment_rank <6
     ORDER BY a_number

' (code to write the data into a csv file. )

When I try to compile this format trigger, I'm getting a Error 103 (Encountered symbol "(" when expecting one of the following: ,form) on the line with dense_rank(), location of the error being the opening bracket before the keyword "partition by". My first guess was that dense_rank() is not supported (I'm using database 10g and Report Builder because dense_rank is not appearing in blue color text like the other keywords..it is appearing in black.

The aim of this report is to pull up top N comments. (Using the field comment_line_id to filter). Is there any workaround for dense_rank() to achieve the same result?

This query works fine in my data model for the report and generates the report correctly. The error occurs only when used in format trigger.

Any help is greatly appreciated.
Re: Dense_Rank not working in Format Trigger [message #376016 is a reply to message #375970] Mon, 15 December 2008 12:38 Go to previous message
Messages: 21042
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here are a few workarounds of How does one select the TOP N rows from a table?; I hope you'll find the one that suits your needs.
Previous Topic: REP-1212- object 'body' is not fully enclosed by its enclosing object 'ROWNUM2'
Next Topic: Oracle report
Goto Forum:

Current Time: Mon Mar 27 17:19:59 CDT 2017

Total time taken to generate the page: 0.08965 seconds