Home » Developer & Programmer » Reports & Discoverer » How to create report (Reports9i, Windows)
How to create report [message #583372] Tue, 30 April 2013 05:49 Go to next message
snsiddiqui
Messages: 142
Registered: December 2008
Senior Member
Dear All

I want to create report using this query, is there any option? please reply.

declare
  cursor c2 is
  select epi_id, epi_code, epi_name
  from   emp_personal_info
  where  epi_status = '32'
  order by epi_code;
  cursor c1 is
  select  distinct eam_datein
  from    emp_atnd_mast
  where   to_char(eam_datein,'fmday') = 'sunday'
  and     eam_datein between '01-APR-2013' and '30-APR-2013'
  order by eam_datein;
  rec c1%rowtype;
  rec1 c2%rowtype;
  satapsnt comm_setups_setup.css_id%type;
  monapsnt comm_setups_setup.css_id%type;  
begin
 for rec1 in c2 loop
  for rec in c1 loop
    begin
      select  eam_status
        into  satapsnt 
       from   emp_atnd_mast
        where eam_epi_id = rec1.epi_id
        and   eam_datein = rec.eam_datein - 1
        and   eam_status = '24';
    exception
     when no_data_found then
     satapsnt := Null;
    end;
    begin
      select  eam_status
        into  monapsnt 
       from   emp_atnd_mast
        where eam_epi_id = rec1.epi_id
        and   eam_datein = rec.eam_datein + 1
        and   eam_status = '24';
    exception
     when no_data_found then
     monapsnt := Null;
    end;
    if satapsnt is not null and monapsnt is not null then
     dbms_output.put_line(rec1.epi_code||' - '||rec1.epi_name||' - '||rec.eam_datein);
    end if; 
  end loop;
 end loop;
end;

[Updated on: Tue, 30 April 2013 05:50]

Report message to a moderator

Re: How to create report [message #583383 is a reply to message #583372] Tue, 30 April 2013 06:32 Go to previous messageGo to next message
Littlefoot
Messages: 19510
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I can tell (as I never created a report based on anything but straight SQL query): if you can rewrite that PL/SQL code into SQL SELECT statement, fine - do so.

Otherwise, create a (global temporary?) table, let the above PL/SQL code populate that table, and then make report simply display what's stored into that table.
Re: How to create report [message #583389 is a reply to message #583383] Tue, 30 April 2013 07:05 Go to previous messageGo to next message
cookiemonster
Messages: 10914
Registered: September 2008
Location: Rainy Manchester
Senior Member
The above code can be rewitten as a single select statement.
Re: How to create report [message #583391 is a reply to message #583389] Tue, 30 April 2013 07:30 Go to previous messageGo to next message
snsiddiqui
Messages: 142
Registered: December 2008
Senior Member
Dear cookiemonster, would you please help me to write the above query in a single select statement.
Re: How to create report [message #583400 is a reply to message #583391] Tue, 30 April 2013 08:02 Go to previous message
cookiemonster
Messages: 10914
Registered: September 2008
Location: Rainy Manchester
Senior Member
Just join the queries. Here's a bit to get you started, replacing the 2 cursors:
select epi.epi_id, epi.epi_code, epi.epi_name
from   emp_personal_info epi
CROSS JOIN (SELECT distinct eam_datein
            from    emp_atnd_mast
            where   to_char(eam_datein,'fmday') = 'sunday'
            and     eam_datein between to_date('01-APR-2013', 'DD-MON-YYYY') and to_date('30-APR-2013', 'DD-MON-YYYY')
           )
where  epi_status = '32';

Now add joins for the 2 select intos.
Previous Topic: Purchase Order Report..
Next Topic: Oracle payables Tds expensewise report issue
Goto Forum:
  


Current Time: Wed Aug 27 16:55:55 CDT 2014

Total time taken to generate the page: 0.07984 seconds