OraFAQ Forum: Reports & Discoverer » how to display a time range

Home » Developer & Programmer » Reports & Discoverer » how to display a time range

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

how to display a time range [message #275775]

Mon, 22 October 2007 11:30

deahayes3
Messages: 203
Registered: May 2006

Senior Member

Help , I dont know how to do this one....

I have a min and max time range in a repeating frame, I am trying to display all the hours in between the min and max time, but I dont know which way to go with this I tried on the data model and creating a function but it doesnt work how I want b/c I am looping through to create the time in between the range, I also tried creating a text field setting it to dsname and placing the below in the format trigger, what am i missing to display the time range. here is my code
thanks in advance

function F_time_rangeFormatTrigger return boolean is
cursor time_cur is SELECT
MAX(TO_char(paint_end_time,'HH24:MI:SS')) maxt --15:00:00
,MIN(TO_char(paint_start_time,'HH24:MI:SS')) mint--07:00:00
FROM time_table_t
WHERE date_of_use = :date_of_use AND site = :p_site
time_rec time_cur%rowtype;

endtime varchar2(25);
vtime date;
time_range varchar2(25);
vstart varchar2(25);
begin
open time_cur;
fetch time_cur into time_rec;
vstart:= time_rec.mint;--7:00:00
Loop
vtime := to_date(vstart,'HH24:MI:SS') + 1/24; --add one hr @ end
endtime:= to_char(vtime, 'HH24:MI:SS');

time_range := vstart||' - '||endtime;
srw.set_field_char(0, time_range);
vstart:= to_char(vtime, 'HH24:MI:SS');-change start time here
exit when vstart = time_rec.maxt;--15:00 = 15:00
end loop;
close time_cur;
return (TRUE);
end;

I want it to look like this if time is between 7:00 to 15:00
then
7:00- 8:00
8:00 - 9:00
9:00 - 10:00
etc
14:00 - 15:00

Report message to a moderator

Re: how to display a time range [message #275788 is a reply to message #275775]

Mon, 22 October 2007 12:49

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

Senior Member
Account Moderator

Please, read the OraFAQ Forum Guide to learn how to properly format your message (using the [code] tags) so that it would be readable.

As of your question: I'd rather use a simple query with help of the CONNECT BY clause. Something like this example (adjusted for the Reports Builder, while the output comes from the SQL*Plus):

SELECT 
  (:start_hour + LEVEL - 1) start_time, 
  (:start_hour + LEVEL) end_time
FROM dual
CONNECT BY LEVEL <= :end_hour - :start_hour; 

START_TIME   END_TIME
---------- ----------
         7          8
         8          9
         9         10
        10         11
        11         12
        12         13
        13         14
        14         15

Report message to a moderator

Re: how to display a time range [message #276916 is a reply to message #275788]

Fri, 26 October 2007 16:14

deahayes3
Messages: 203
Registered: May 2006

Senior Member

thanks for responding, but I didnt get this result, my result was only one row, it showed only the end result and it didnt work in report builder. All I want to do is create a loop to display hours between a given range and have them displayed on the report

Report message to a moderator

Re: how to display a time range [message #276917 is a reply to message #276916]

Fri, 26 October 2007 16:26

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

Senior Member
Account Moderator

I've written a report using Report Builder 10g Data Block wizard; SQL query was copy-pasted from my previous post. A 30-seconds report. This is the result:

/forum/fa/3303/0/

Which database and developer version do you use? On 10g it works like a charm.

If this simple approach can not be applied in your environment, you might:
- create a (global?) temporary table
- create two report parameters: start and end hour
- compute intervals in the AFTER PARAMETER FORM trigger and write them into the temporary table
- use those values in report's query

Attachment: interval.PNG
(Size: 4.74KB, Downloaded 1277 times)

Report message to a moderator

Re: how to display a time range [message #277304 is a reply to message #276917]

Mon, 29 October 2007 08:38

deahayes3
Messages: 203
Registered: May 2006

Senior Member

I am using developer 9i, database 10g
the two values I used was 7 and 15 I get error ora-01436 , if I use 7 and 3 I dont get a error but it only displays
start time --- end time
7 --------------------- 8

I will try the other way you suggested.....

[Updated on: Mon, 29 October 2007 08:40]

Report message to a moderator

Re: how to display a time range [message #277331 is a reply to message #277304]

Mon, 29 October 2007 11:07

deahayes3
Messages: 203
Registered: May 2006

Senior Member

Whats a global temporary table?
I created a table and insert the range to the table, thats fine. but I just realized this can not work because in the report the time range changes so I can just insert value in the parameter form, the start and end times changes on every page of the report, which is causing the below not to display on the report .
I get the following error
REP-1814: Report cannot be formatted. Object '<object name>' can never fit within '<location>'.

I placed the following code in a formula because I needed to pull the date for every page.
Please can anyone tell me what I am doing wrong.

delete from timeranges_rpt_tbl;
commit;

while vstart <= vstop
loop
insert into timeranges_rpt_tbl values(vstart,vstart+1.00);
commit;
vstart:= vstart + 1.00;
end loop;

Report message to a moderator

Re: how to display a time range [message #277374 is a reply to message #277331]

Mon, 29 October 2007 16:27

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

Senior Member
Account Moderator

If start and end time change, I believe they change depending on something (for example, employee_id, product_id, ...). Could you expand the table you are using and enter this additional information ("product_id") along with time intervals, and later select those values in the main report query; something like

SELECT i.start_time, i.end_time, t.column_1
FROM interval i, table t
WHERE i.product_id = t.product_id
  AND ...
ORDER BY i.start_time

And, really, what might this global temporary table be? /forum/fa/1600/0/

Also, is upgrading Developer Suite to a more recent version feasible solution to you?

Report message to a moderator

Re: how to display a time range [message #277578 is a reply to message #277374]

Tue, 30 October 2007 11:45

deahayes3
Messages: 203
Registered: May 2006

Senior Member

ok, Im getting closer Smile

, I did what you said I combined the two tables but Im now getting duplicates for some reason, and I dont see why. I attached an excel spreadsheet so you can see whats going on...
P.S. theres no chance of upgrading developer.

Attachment: timerange_data.xls
(Size: 172.00KB, Downloaded 1725 times)

Report message to a moderator

Re: how to display a time range [message #277591 is a reply to message #277578]

Tue, 30 October 2007 13:13

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

Senior Member
Account Moderator

... and there's no chance for downloading an XLS file. Sorry. Post a screenshot (JPG or PNG) or a pure TXT file instead. Or, even better, a test case (CREATE TABLE and INSERT INTO sample data, along with a RDF file) so that we could see what's going on.

Forgot to say ... would a simple SELECT DISTINCT solve that duplicate records problem?

[Updated on: Tue, 30 October 2007 13:14]

Report message to a moderator