Home » Developer & Programmer » Reports & Discoverer » Create Report From Cursor (Oracle 10g, Windows)
Create Report From Cursor [message #557962] Mon, 18 June 2012 05:22 Go to next message
snsiddiqui
Messages: 172
Registered: December 2008
Senior Member
I want to create report against my below query, please suggest how?

 Declare
   Cursor Emp_Cur is
   Select epi_id, epi_code
   From emp_personal_info
   Where epi_status = '73'
   Order By epi_id ;

   Cursor Atnd_Cur(epi_id varchar2) is
   Select emp_data, eam_status
   From emp_attnd_mast
   Where eam_date between '01-JUN-2012' and '18-JUN-2012'
   And eam_epi_id = epi_id
   Order By eam_date ;

   Val1 Number ;
   Rec1 Emp_Cur%RowType;
   Rec2 Atnd_Cur%RowType;
   EmpNo emp_personal_info.epi_code%Type;
Begin
  For Rec1 in Emp_Cur Loop
   For Rec2 in Atnd_Cur(Rec1.epi_id) Loop
    If Rec2.eam_status = '94' Then
     Val1 := Val1 + 1;
     If Nvl(Val1,0) > = 5 Then
      dbms_output.put_line(Rec1.epi_code);
     End If;
    Else
     Val1 := 0;
    End If;
   End Loop Rec2;
  End Loop Rec1;
End;
Re: Create Report From Cursor [message #557967 is a reply to message #557962] Mon, 18 June 2012 06:20 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I suppose that values you'd want to display in report are REC1.EPI_CODE.

Therefore, create a (global temporary) table which will contain that column (you know its datatype, I suppose). Then put code you already posted into report's After Parameter Form trigger. Instead of DBMS_OUTPUT.PUT_LINE, do INSERT INTO newly created table.

Report's query should select values from the table and display them.
Re: Create Report From Cursor [message #557970 is a reply to message #557967] Mon, 18 June 2012 06:37 Go to previous messageGo to next message
snsiddiqui
Messages: 172
Registered: December 2008
Senior Member
Thanks, Littlefoot

OK, I check the syntax for global temporary table.

Would you please also suggest me for the right query, actually I want to get continuous absent employees list against my given date range and number of days.
Re: Create Report From Cursor [message #557974 is a reply to message #557970] Mon, 18 June 2012 06:55 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you could provide a test case, someone would have a look.
Previous Topic: Oracle Reports v 6 - printing on pre-printed stationary
Next Topic: ORACLE REPORT PLACEHODER COLUMN (merged 2)
Goto Forum:
  


Current Time: Sat Nov 30 05:11:01 CST 2024