OraFAQ Forum: Reports & Discoverer » Using a parameter for Maximum rows to fetch

Home » Developer & Programmer » Reports & Discoverer » Using a parameter for Maximum rows to fetch (Oracle Report Builder 10.1.2.0.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Using a parameter for Maximum rows to fetch [message #486412]

Wed, 15 December 2010 07:34

davholla
Messages: 116
Registered: August 2009
Location: London

Senior Member

In Report it is possible to set the maximum rows to fetch to be a number e.g. 1+.
Is it possible to use a number that is selected from another query?

When I try to do this i.e. I put in the field num_lines or :num_lines I just get invalid number

Report message to a moderator

Re: Using a parameter for Maximum rows to fetch [message #486487 is a reply to message #486412]

Thu, 16 December 2010 00:51

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Check SRW.SET_MAXROW built-in (in Reports Builder online documentation). This is a procedure that sets the maximum number of records to be fetched for the specified query.

Report message to a moderator

Re: Using a parameter for Maximum rows to fetch [message #486552 is a reply to message #486487]

Thu, 16 December 2010 08:44

davholla
Messages: 116
Registered: August 2009
Location: London

Senior Member

Thanks a lot for that. Here is the before reports trigger where should I put

SRW.SET_MAXROW('Q_1',1);

Function BeforeReport return boolean is

begin

 

BEGIN

 

  -- srw.do_sql('alter session set sql_trace = true');

  select displayed_field

  into   :C_continued

  from   po_lookup_codes

  where  lookup_type = 'POXPRPOP'

  and    lookup_code = 'CONTINUED';

 

  select displayed_field

  into   :C_address_at_top

  from   po_lookup_codes

  where  lookup_type = 'POXPRPOP'

  and    lookup_code = 'ADDRESS AT TOP';

/* Bug 1484478

  The call to initialize is being made in after parameter value trigger and it

  is not necessary to do it here again

  SRW.USER_EXIT('FND SRWINIT');

*/

 

/* Change the :P_single_po_print parameter value

to 1 if po_from and po_to parameters are same.

Then Query will fetch the correct data for all conditions and uses the

indexes incase of :P_po_num_from=:P_po_num_to and ALPHANUMERIC data

Bug#946470 -- bgopired  */

 

 

  If :P_po_num_from = :P_po_num_to THEN

            :P_single_po_print := 1;

  END IF;

 

 

  srw.user_exit('FND INSTALLATION OUTPUT_TYPE="STATUS"

                 OUTPUT_FIELD=":P_EC_STATUS"

                 APPS="EC"');

 

  srw.message ('010', :P_ec_status);

 

  SRW.USER_EXIT('FND FLEXSQL CODE           ="MSTK"

                             OUTPUT         =":P_FLEX_ITEM"

                             APPL_SHORT_NAME="INV"

                             MODE           ="SELECT"

                             DISPLAY        ="ALL"

                             NUM            ="101"

                             TABLEALIAS     ="MSI"');

 

/* Check for multilingual installation and take appropriate action. */

/* See Reports PL/SQL for more details. */

  if (MLS_INSTALLED) then

     POPULATE_MLS_LEXICALS;

  end if;

 

  RETURN TRUE;

END;  return (TRUE);

end;

Report message to a moderator

Re: Using a parameter for Maximum rows to fetch [message #486554 is a reply to message #486552]

Thu, 16 December 2010 09:31

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

Any point before the return I would imagine.

That 2nd return true is pointless btw.

Report message to a moderator

Re: Using a parameter for Maximum rows to fetch [message #486557 is a reply to message #486554]

Thu, 16 December 2010 10:25

davholla
Messages: 116
Registered: August 2009
Location: London

Senior Member

Thanks for that I modified it as at the bottom of the page -

But I get

REP-1316: Before Report Trigger references column 'c_num_lines' at wrong frequency

function BeforeReport return boolean is

begin

 

BEGIN

 

  -- srw.do_sql('alter session set sql_trace = true');

  select displayed_field

  into   :C_continued

  from   po_lookup_codes

  where  lookup_type = 'POXPRPOP'

  and    lookup_code = 'CONTINUED';

 

  select displayed_field

  into   :C_address_at_top

  from   po_lookup_codes

  where  lookup_type = 'POXPRPOP'

  and    lookup_code = 'ADDRESS AT TOP';

/* Bug 1484478

  The call to initialize is being made in after parameter value trigger and it

  is not necessary to do it here again

  SRW.USER_EXIT('FND SRWINIT');

*/

 

/* Change the :P_single_po_print parameter value

to 1 if po_from and po_to parameters are same.

Then Query will fetch the correct data for all conditions and uses the

indexes incase of :P_po_num_from=:P_po_num_to and ALPHANUMERIC data

Bug#946470 -- bgopired  */

 

 

  If :P_po_num_from = :P_po_num_to THEN

            :P_single_po_print := 1;

  END IF;

 

 

  srw.user_exit('FND INSTALLATION OUTPUT_TYPE="STATUS"

                 OUTPUT_FIELD=":P_EC_STATUS"

                 APPS="EC"');

 

  srw.message ('010', :P_ec_status);

 

  SRW.USER_EXIT('FND FLEXSQL CODE           ="MSTK"

                             OUTPUT         =":P_FLEX_ITEM"

                             APPL_SHORT_NAME="INV"

                             MODE           ="SELECT"

                             DISPLAY        ="ALL"

                             NUM            ="101"

                             TABLEALIAS     ="MSI"');

 

/* Check for multilingual installation and take appropriate action. */

/* See Reports PL/SQL for more details. */

  if (MLS_INSTALLED) then

     POPULATE_MLS_LEXICALS;

  end if;

  SRW.SET_MAXROW('Q_headers',:c_num_lines);

  RETURN TRUE;

 

end;

end;

Report message to a moderator

Re: Using a parameter for Maximum rows to fetch [message #486558 is a reply to message #486557]

Thu, 16 December 2010 10:42

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

So where does c_num_lines come from?
If it's from one of the report queries then you can't use it as it won't be populated until after this trigger has finished executing

Report message to a moderator

Re: Using a parameter for Maximum rows to fetch [message #486559 is a reply to message #486558]

Thu, 16 December 2010 11:02

davholla
Messages: 116
Registered: August 2009
Location: London

Senior Member

cookiemonster wrote on Thu, 16 December 2010 16:42

So where does c_num_lines come from?
If it's from one of the report queries then you can't use it as it won't be populated until after this trigger has finished executing

It does come from a query. So how should I do this - create a format trigger?

Report message to a moderator