Click here to Skip to main content
15,946,012 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How do I print just 5 pages only based on this query below. It renders sometimes a full month or even 4 days when i try the following dates and disregards the actual date range. The query is linked to Jasper and passes a date range as parameters

to_date('16032020','DDMMYYYY') - to_date('20032020','DDMMYYYY')
to_date('23032020','DDMMYYYY') - to_date('27032020','DDMMYYYY')
to_date('30032020','DDMMYYYY') - to_date('03042020','DDMMYYYY')
to_date('06042020','DDMMYYYY') - to_date('10042020','DDMMYYYY')



Jasper Parameter[^]

Actual Query
select e.employee_id,
       e.initials || ' ' || regexp_replace(e.surname, '\([^()]*\)', '') farm_manager,
       r.research_station_id,
       r.name,
       dr.attendance_date attendance_date
  from research_station r
  join employees e
    on (e.employee_id = r.farm_manager)
 cross join (select ($P{Date_from} + rownum - 1) attendance_date
               from dual
             connect by level <= ($P{Date_from} - sysdate + 1)) dr
  join employee_jobs ej
    on (e.employee_id = ej.employee_id)
 where r.research_station_id = $P{Research_Station_id}


What I have tried:

Test Query
select e.employee_id,
       e.initials || ' ' || regexp_replace(e.surname, '\([^()]*\)', '') farm_manager,
       r.research_station_id,
       r.name,
       dr.attendance_date attendance_date
  from research_station r
  join employees e on (e.employee_id = r.farm_manager)
 cross join (select (to_date('16032020','DDMMYYYY') + rownum - 1) attendance_date from dual 
 connect by level <= to_date('20032020','DDMMYYYY') - sysdate + 1) dr
  join employee_jobs ej on (e.employee_id = ej.employee_id)
 where r.research_station_id = 2;
Posted
Updated 16-Mar-20 1:01am
v2
Comments
Jörgen Andersson 18-Mar-20 11:36am    
Normally you limit the number of rows by using OFFSET FETCH NEXT.

If you have an older version of oracle you need to use Rownum.

What version is your Oracle?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900