Home » Developer & Programmer » Reports & Discoverer » print random record on report (oracle 10g, form/ reports 6i)
print random record on report [message #449316] Mon, 29 March 2010 03:29 Go to next message
wazir
Messages: 18
Registered: February 2010
Junior Member
Hi ALL,

I want to print random record on reports..

for example i have one table of recieves and i want to print only recpit no: 1,4 7,100...

How can i pass these id to report so only these records will print..

Thankx in advance...



CM: fixed typo in title

[Updated on: Mon, 29 March 2010 04:01] by Moderator

Report message to a moderator

Re: print rendom record on report [message #449318 is a reply to message #449316] Mon, 29 March 2010 03:45 Go to previous messageGo to next message
Littlefoot
Messages: 21564
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you "pass ID" to the report, then it is no longer "random" but predefined. If that's your question, well, you'd use a parameter. For example:
select ename, job, sal
from emp
where empno = :par_empno


On the other hand, if you really want to select random values, you should perhaps try to use DBMS_RANDOM package. Something like this:
select ename, job, sal
from (select ename, job, sal
      from emp
      order by dbms_random.value
     )
where rownum = 1
It works OK in Reports 10g; I don't have version 6i to test, but you can try.
Re: print random record on report [message #449336 is a reply to message #449316] Mon, 29 March 2010 04:21 Go to previous messageGo to next message
wazir
Messages: 18
Registered: February 2010
Junior Member
@littlefoot

Sir actully what i want is..

i want to pass different values (record_id's) to report through parameter and only wants to print that records only..

i already make reports to which i pass one perameter and it print only one record..

for example i pass rec_id=100 it will print only rec_id =100

SELECT * FROM PU_DOC_REC where PU_DOC_REC.REC_ID =:p_rec_no1

and in an other report i pass two parameter it prints all records between them..

for example i pass rec_id=100 rec_id = 105 and it will print all the records from rec_id =100 to 105

SELECT * FROM PU_DOC_REC where (PU_DOC_REC.REC_ID between :p_rec_no1 and :p_rec_no2)


but now i wants to pass multipul (rendom means without any sequence i.e 101,103,105 etc) record_id's to it print only that record..

i dont know how can i do this..how to pass different value
Re: print random record on report [message #449339 is a reply to message #449316] Mon, 29 March 2010 04:26 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
use a lexical parameter
Re: print random record on report [message #449385 is a reply to message #449316] Mon, 29 March 2010 06:22 Go to previous messageGo to next message
wazir
Messages: 18
Registered: February 2010
Junior Member
@cookiemonster sir thankx alot..

yes i go through the post alreday aviable on forum related to lexical perameter..

and it really helps me...

Thankx alot again and also i am thankfull for kind help from littlefoot..

i have here a little question

this code runs fine for number feild but for char we have to put the value in qouts in the parameter.. i try to apply tochar(:p_rec_no1) but it give error of "too many declaration"


function AfterPForm return boolean is
begin
if :p_rec_no1 is not null then
:rec_no := 'where R_REC_ID in (' || to_char(:p_rec_no1) ||')';
end if;

return (TRUE);
end;


can any body tell give me any idea...
Re: print random record on report [message #449395 is a reply to message #449385] Mon, 29 March 2010 06:40 Go to previous messageGo to next message
Littlefoot
Messages: 21564
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You need to use double single quotes, or - if it is too messy, concatenate CHR(39 with the rest of the string. Something like
:rec_no := 'where R_REC_ID in (' ||chr(39)|| :p_rec_no1 ||chr(39)|| ')';

When you aren't certain whether it is OK, print a string (rec_no in your case)! In SQL*Plus you'd use DBMS_OUTPUT.PUT_LINE. In Reports use SRW.MESSAGE built-in.
Re: print random record on report [message #449622 is a reply to message #449316] Wed, 31 March 2010 00:41 Go to previous messageGo to next message
wazir
Messages: 18
Registered: February 2010
Junior Member
Sir i try

:rec_no := 'where R_REC_ID in (' ||chr(39)|| :p_rec_no1 ||chr(39)|| ')';


but it works fine only for onr record but for more then one record it gives nothing....
Re: print random record on report [message #449623 is a reply to message #449622] Wed, 31 March 2010 01:00 Go to previous messageGo to next message
Littlefoot
Messages: 21564
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Does it mean that P_REC_NO1's values can be, for example:
- 101 (works OK)
- 105 (works OK)
- 101, 105 (doesn't work)

I tested it on Scott's schema. Query was
select deptno, dname, loc
from dept
&lex_where

Lexical parameter's value was computed in After_parameter_form trigger, based on input parameter I called PAR_DEPTNO (character, length = 40) as follows:
function AfterPForm return boolean is
begin
  :lex_where := 'where deptno in (' || :par_deptno ||')';
  return (TRUE);
end;

When ran, it seems to be working properly:

/forum/fa/7648/0/

/forum/fa/7649/0/

What and how did you do the job?

  • Attachment: lex_1.PNG
    (Size: 6.63KB, Downloaded 468 times)
  • Attachment: lex_2.PNG
    (Size: 2.07KB, Downloaded 349 times)
Re: print random record on report [message #449641 is a reply to message #449316] Wed, 31 March 2010 03:02 Go to previous message
wazir
Messages: 18
Registered: February 2010
Junior Member
@Littlefoot

Yes sir it is working...

Thankx alot for your kind help...

regards
wazir
Previous Topic: One Report in two diffrent Printer
Next Topic: enable disable print option in between pages trigger
Goto Forum:
  


Current Time: Fri Jun 05 15:23:18 CDT 2020