Converting number to hours/miniutes/seconds/subseconds
Tony, July 18, 2002 - 10:43 pm UTC
Tom,
Could you please show with the example to get sum of activity duration stored in number format in the oracle DB (8.1.6). But the input is from an ASCII file in HHMISSSS format.
Input (through ASCII file)--> 00054000 (three records)
stored in DB in number(8) format -->54000
O/P required in the format HHMISSSS = 00170000
Thanks
July 19, 2002 - 7:18 am UTC
I don't understand what
HH MI SSSS
could be as a format? Further, I don't see how 54000, using that mask, could result in 00170000 either?
A Reader
Rahul, July 22, 2002 - 1:55 am UTC
Hi Tom
Might be this is a very silly question but I want to know that how can we add minutes or seconds in time part of date.
suppose current date is showing 23-jul-02 05:45:33 , but at
the time of insertion in the table I want to send 10 seconds ahead like 23-jul-02 05:45:43.
How can we do this??
Thanks.
July 22, 2002 - 9:38 am UTC
date + 1 = date + one day
date + 1/24 = date + one hour
date + 1/24/60 = date + one minute
date + 1/24/60/60 = date + one second
so, date + 10 * (1/24/60/60) = 10 seconds from now.
TIME only
Jeff, February 28, 2003 - 2:21 pm UTC
Great!
Question - how can I store just a time with no date?
In other words I'm tracking shift start and stop times like 8:00 AM and 5:00 PM. This is a daily schedule so I don't want to save the day part just the time part.
Does it make sense to save in Date/Time data type with a low values for date?
Thanks,
Jeff
February 28, 2003 - 2:34 pm UTC
Just pick a date and use it
01010001 (january 1, year 1 is one I've used)
In 9i you could use an interval -- start_time_interval = 8hours. duration = 9hours.
That way -- any "day" plus start_time_interval would give you 8am on that day.
any "day" plus start_time_interval + duration would give you 5pm.
Time Enter as HH12:Mi
Jamil, October 12, 2003 - 9:59 am UTC
Hi Tom
I have a table that is used for the driver travelling information date and time so, the structure of the table as follow
Travel_table
-------------
Car_no varchar2(15)
Driver_code number
Tr_date date
Tr_time number
So I want the user to enter the travel time as HH12:MI and calculated and get the out put as follow
DAYS HOURS MINUTES
---------- ---------- -----------------
0 7 27
can you give an example in detail.
Thank you very much for you help
Best Regards
Jamil
October 12, 2003 - 10:27 am UTC
not following you.
if they give you hh12:mi (which would be a bug -- what exactly is 01:01 -- 1am or 1pm) - you
a) have 0 days always
b) have the hours (they gave you hh12)
c) have the minutes (they gave you mi)
I'm not seeing what the issue is? you have everything you need?
Adding Time part in Date Field
Jamil, October 13, 2003 - 1:52 am UTC
Dear Tom
What I am trying to say, that the user want to enter the date
as 1 PM not as 13 PM and 2 PM Not as 14 PM
and 3:30 PM not as 15:30 PM and so on. I am very sorry if my question was not clear.
As you can see the field that I want the user to enter the time in it is defined as follow
Tr_time number .
Would you give an example how to do it.
And thank you very much for your valuable answer.
Best regards
Jamil
October 13, 2003 - 7:25 am UTC
sorry -- not getting it. so the user gives you 1 pm.
now what? what do you want to do with that? what would that look like in a "number".
i would be storing in a date (or interval type in 9i).
but it is pretty easy to turn 1:30 pm into 13 hours, 30 minutes:
1 select to_char( to_date('1:30 pm', 'hh:mi am'), 'hh24' ) hours,
2 to_char( to_date('1:30 pm', 'hh:mi am'), 'mi' ) minutes
3* from dual
ops$tkyte@ORA920> /
HO MI
-- --
13 30
Getting the difference between two times as the format HH12:MI
Jamil, October 14, 2003 - 5:19 am UTC
Dear Tom
I am very sorry for not understanding my problem I have change my table structure as follow
Travel_table
CAR_NO VARCHAR2(15)
DRIVER_NO NUMBER
START_DATE DATE --> for starting travailing date
END_DATE DATE --> for ending the journey date
START_TIME DATE --> for starting travailing TIME
END_TIME DATE --> for ending the journey TIME
So I want the system to display the "Calculating time between two date/timestamp "
my date format is 'HH12:MI'.
Please do not change the date format
So the user will enter the data as follow
Enter value START_TIME: 12:45
Enter value for END_TIME: 01:05
The required out put as follow:
The output should be as follow :
DAYS HOURS MINUTES
---------- ---------- ----------
0 0 20
Enter value START_TIME: 11:45
Enter value for END_TIME: 01:05
The output should be as follow :
DAYS HOURS MINUTES
---------- ---------- ----------
0 1 20
Enter value START_TIME: 12:00
Enter value for END_TIME: 01:00
The output should be as follow :
DAYS HOURS MINUTES
---------- ---------- ----------
0 1 0
Thanks a lot for your cooperation .
Best regards
Jamil
Adding Time part in Date Field
Jamil, October 15, 2003 - 3:55 am UTC
Dear Tom
Would you please give Detail example of using HH12:MI instead of HH24:MI ,because I am installing the START_TIME , END_TIME and START_DATE and END_DATE in the database as HH12:MI format and I am not understand
how I can use this HH24:MI format in your example
create or replace function datediff( p_what in varchar2,
p_d1 in date,
p_d2 in date ) return number
as
l_result number;
begin
select (p_d2-p_d1) *
decode( upper(p_what),
'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL )
into l_result from dual;
return l_result;
end;
and also consider that I am entering the other two filed
START_DATE DATE --> for starting travailing date
END_DATE DATE --> for ending the journey date
the user enter the data as the following :
Enter value for START_DATE : 01-03-2003
Enter value for END_DATE : 01-03-2003
Enter value for START_TIME : 12:00
Enter value for END_TIME : 01:00
so As I said before I want to get the output as the following :
DAYS HOURS MINUTES
---------- ---------- ----------
0 1 0
Thank you very much for your help
Best regards
Jamil
October 15, 2003 - 7:56 am UTC
you are storing it in 24 hour format -- dates are a 7 byte internal format that always have
YYYY
MM
DD
HH24
MI
SS
don't get confused by format masks - they are used simply to parse data on the way in and format it on the way out.
they have NOTHING to do with HOW the data is stored.
just do it the way I described above and you'll get what you asked for.
How ?
Ashiq Shamsudeen A, October 15, 2003 - 9:11 am UTC
What's the reason behind this..
scott@ASHIQ>create table a (dt date)
/
Table created.
scott@ASHIQ> insert into a values('01-jan-02');
1 row created.
scott@ASHIQ> ed
Wrote file afiedt.buf
1* insert into a values('01-jan-99')
scott@ASHIQ> /
1 row created.
Here, I've inserted the date(for the testing purpose I've not give the format)
scott@ASHIQ> select to_char(dt, 'dd/mm/yyyy hh:mi:ss am')dt
from a
DT
----------------------
01/01/2002 12:00:00 am
01/01/1999 12:00:00 am
2 rows selected.
Why its treating "99" as "1999"?. But if I give the format..
scott@ASHIQ> rollback;
Rollback complete.
scott@ASHIQ>insert into a values(to_date('01-jan-99', 'dd-mon-yy'))
scott@ASHIQ> select to_char(dt, 'dd/mm/yyyy hh:mi:ss am')dt from a;
DT
----------------------
01/01/2099 12:00:00 am
1 row selected.
Here its taking "99" as "2099"
October 15, 2003 - 6:31 pm UTC
this is a CLASSIC example of why it is a really bad, sadly bad idea to use 2 characters to try and enter something that takes 4 at least!!!!!!!!!!!!
we should all have learned this in 1999.
anyway, your default date format must be dd-mon-rr
rr says "use a sliding 50 year window".
yy says "use this century"
ops$tkyte@ORA920LAP> select year, to_char( to_date(year,'rr'), 'yyyy' ), to_char( to_date(year,'yy'), 'yyyy' )
2 from t;
YE TO_C TO_C
-- ---- ----
50 1950 2050
60 1960 2060
70 1970 2070
80 1980 2080
90 1990 2090
99 1999 2099
01 2001 2001
10 2010 2010
20 2020 2020
30 2030 2030
40 2040 2040
11 rows selected.
see -- the year 50 -- today in 2003 -- resolves to 1950 using RR, 2050 using YY
All of the dates will be 20xx using YY
The dates will be a mix of 19xx and 20xx using RR
If you take this piece of advice you will NEVER EVER be sorry you did (promise)
a) always insist on 4 characters for a year
b) always use EXPLICIT conversions from strings to dates (to_date always)
Selecting rows of the prior hour and grouping by every minute
A reader, December 04, 2003 - 10:39 am UTC
Hi Tom,
I have a table:
Create table ntwk_stats (delay_secs number, insert_time date);
with the following data:
insert into ntwk_stats values(20, 12-02-2003 9:59:20);
insert into ntwk_stats values(18, 12-02-2003 9:59:50);
insert into ntwk_stats values(20, 12-02-2003 10:53:21);
insert into ntwk_stats values(10, 12-02-2003 10:53:49);
insert into ntwk_stats values(22, 12-02-2003 10:54:10);
insert into ntwk_stats values(20, 12-02-2003 10:54:40);
insert into ntwk_stats values(20, 12-02-2003 11:01:01);
insert into ntwk_stats values(22, 12-02-2003 11:01:03);
and another table:
Create table avg_ntwk_stats (avg_delay_secs number, stats_time date);
At any given time, I have to find avg(delay_secs) in the prior hour, group them by every minute and add insert them into avg_ntwk_stats table, i.e., if the sysdate is say
12-02-2003 11:03:40 AM, I should only be selecting records between 10:00:00 and 10:59:59 AM and group them by minute, such that the following rows get inserted in avg_ntwk_stats table :
15, 12-02-2003 10:53:00
21, 12-02-2003 10:54:00
I am having trouble selecting data that falls in the prior hour and then grouping by every minute. Can you help?
Thanks in advance for your input.
December 04, 2003 - 11:08 am UTC
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select avg(delay_secs), trunc(insert_time,'mi')
2 from ntwk_stats
3 where insert_time between trunc( to_date('12-02-2003 11:03:40'),'hh')-1/24
4 and trunc( to_date('12-02-2003 11:03:40'),'hh')-1/24/60/60
5 group by trunc(insert_time,'mi')
6 /
AVG(DELAY_SECS) TRUNC(INSERT_TIME,'
--------------- -------------------
15 12-02-2003 10:53:00
21 12-02-2003 10:54:00
replace to_date('12-02-2003 11:03:40') with sysdate
Just wanted to Thank You!
A reader, December 05, 2003 - 10:44 am UTC
This is great! I really appreciate this.
NLS_LANG Problem
Jamil, February 25, 2004 - 6:20 am UTC
Dear Sir,Tom
What is the equivalent of the AM and PM for Arabic system date and time , I have the above trigger and when I change the NLS_LANGE to ARABIC_UNITED ARAB EMIRATES.AR8MSWIN1256
I am getting the wrong result , but if I change the NLS_LANGE TO AMERICAN_AMERICA.ar8mswin1256 it works ,because the above travel_time function is not working ,when I change to Arabic NLS_LANg ,because of the below statement
So I want to make the change for the AM & PM in the following statement :
--------------- BUSH BUTTON
BEGIN
ST_DATE := TO_CHAR(:J_START_DATE,'MM-DD-RRRR');
ST_TIME := RTRIM(LTRIM(:J_START_TIME));
IF :J_START_TIME_STATUS = 1 THEN
ST_TIME_STATUS :='AM';
ELSIF :J_START_TIME_STATUS = 2 THEN
ST_TIME_STATUS :='PM';
END IF;
EN_DATE :=to_char(:J_END_DATE ,'MM-DD-RRRR');
EN_TIME := RTRIM(LTRIM(:J_END_TIME));
IF :J_END_TIME_STATUS = 1 THEN
EN_TIME_STATUS :='AM';
ELSIF :J_END_TIME_STATUS = 2 THEN
EN_TIME_STATUS :='PM';
END IF;
DATA_RET :=travel_time(ST_DATE,ST_TIME,ST_TIME_STATUS,
EN_DATE,EN_TIME,EN_TIME_STATUS);
MESSAGE('Time Calculated='||DATA_RET);
END;
-------------------------
FUNCTION travel_time (sd varchar2,st varchar2,s_ampm varchar2,
ed varchar2,et varchar2,e_ampm varchar2)
RETURN varchar2
IS
s_dt date;
e_dt date;
rtrn varchar2(50);
ET1 NUMBER; -- Elapsed time
Begin
begin
s_dt := to_date(sd || st || s_ampm,'MM-DD-YYYYHH:MIAM');
exception when others then
MESSAGE('Invalid start date/time: '||sd||' '||st||s_ampm);
rtrn := 'Invalid start date/time: '||sd||' '||st||s_ampm;
end ;
if rtrn is null then
begin
e_dt := to_date(ed || et || e_ampm,'MM-DD-YYYYHH:MIAM');
exception when others then
MESSAGE('Invalid end date/time: '||ed||' '||et||e_ampm);
rtrn := 'Invalid end date/time: '||ed||' '||et||e_ampm;
end;
end if;
if rtrn is null then
ET1 := e_dt - s_dt;
rtrn := 'D'||Trunc(ET1)||' '
||'H'||Mod(Trunc(ET1*24),24)||' '
||'M'||Mod(Round(ET1*1440),60);
end if;
return rtrn;
End travel_time;
Best regards
Jamil
February 25, 2004 - 9:23 am UTC
I've no idea at all. sorry.
A reader, June 01, 2004 - 2:08 pm UTC
Hi Tom,
I've two date column 1) start_dt 3 ) start_time.
I've to extract date portion from start_dt and Time portion from start_time and combine two in other field... Plan_date.
For eg.
start_Date start_time Plan_date
5/15/04 12:00 5/16/04 16:00 5/15/04 16:00
How can I do that?
Thanks
June 01, 2004 - 3:51 pm UTC
update t
set plan_date = to_date( to_char( start_date, 'mm/dd/yyyy' ) ||
to_char( start_time, 'hh24:mi:ss' ),
'mm/dd/yyyyhh24:mi:ss' );
dates
mo, November 18, 2005 - 4:52 pm UTC
Tom:
Can you update the year parts of a date only?
I have some dates with '10-JAN-0005'
I want to fix it to '10-JAN-2005'
how do you do it
November 19, 2005 - 9:50 am UTC
substr would be my likely choice.
Or date math, if you new you wanted to add 2000 years - you could use add months to add 2000*12 months.
ops$tkyte@ORA9IR2> select add_months(to_date('10-jan-0005','dd-mon-yyyy'), 2000*12 ) from dual;
ADD_MONTHS(TO_DATE('
--------------------
10-jan-2005 00:00:00
How to do this?
A reader, January 04, 2006 - 2:46 pm UTC
Tom,
I have action date (in date data type) and time (in number data type) in a table as separate columns. I want to convert into actual date and time. The data is like this
action_date -> '01-JAN-2005'
action time -> 6222325 (i.e, 6 hours, 22 minutes, 23 seconds, and 25 sub seconds), also hours are in 24 hour clock, but 0 is truncated because of number data type.
How can I get
date with time data together? i.e., I want to get
date and time in the new date column.
Regards
January 04, 2006 - 3:02 pm UTC
do you want a date or a timestamp.
dates only support seconds, just add 6/24 + 22/24/60 + 23/24/60/60 (eg: math, if you add 1 to a date you get the next day at the same time, 1/24 - add an hour and so on)
timestamp, not much different
however, consider "an interval" in the future.
Timestamp
A reader, January 04, 2006 - 3:41 pm UTC
Thanks for quick response.
We need timestamp, and we are using 9ir2. Can we use something like this?
action_date||to_date(time with some timestampformat(?))
Regards
January 04, 2006 - 5:15 pm UTC
as long as the hours/minutes/seconds/hseconds are such that
hours <= 24
minutes <= 59
.... and so on
(eg: they are really hh24missff, then:
ops$tkyte@ORA10GR2> select to_timestamp( to_char( x, 'dd-mm-yyyy' ) || to_char(y/100,'fm000000.00'), 'dd-mm-yyyyhh24miss.ff' )
2 from t;
TO_TIMESTAMP(TO_CHAR(X,'DD-MM-YYYY')||TO_CHAR(Y/100,'FM000000.00'),'DD-MM-Y
---------------------------------------------------------------------------
01-JAN-05 06.22.23.250000000 AM
Thanks a lot!
A reader, January 04, 2006 - 5:31 pm UTC
A reader, January 25, 2006 - 1:07 pm UTC
Thanks
Time store as varchar
A reader, May 23, 2007 - 3:32 am UTC
Hi Tom,
I have a table that store the shift start and end time for a roster as varchar2. The format of the time is HH24:MI.
Fortunately, the shift does not extend longer than 24 hours, but it could start in the evening and goes to the next day. For example, the shift start monday 9pm and finished Tuesday 2am, so the times will be 21:00 and 02:00.
Is there any date functions to calculate the shift duration for the above example?
Thanks very much.
May 23, 2007 - 7:04 am UTC
yes, fortunate - your really bad model works "by accident"
ops$tkyte%ORA10GR2> create table t ( x varchar2(4), y varchar2(4) );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( '0900', '1700' );
1 row created.
ops$tkyte%ORA10GR2> insert into t values ( '2300', '0700' );
1 row created.
ops$tkyte%ORA10GR2> insert into t values ( '1000', '0900' );
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @bigdate
ops$tkyte%ORA10GR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
ops$tkyte%ORA10GR2> select in_time, out_time, out_time-in_time
2 from (
3 select to_timestamp(x,'hh24mi') in_time,
4 to_timestamp(y,'hh24mi')+
5 case when to_timestamp(y,'hh24mi') < to_timestamp(x,'hh24mi')
6 then interval '1' day
7 else interval '0' day
8 end out_time
9 from t
10 )
11 /
IN_TIME
---------------------------------------------------------------------------
OUT_TIME
---------------------------------------------------------------------------
OUT_TIME-IN_TIME
---------------------------------------------------------------------------
01-MAY-07 09.00.00.000000000 AM
01-MAY-07 05.00.00.000000000 PM
+000000000 08:00:00.000000000
01-MAY-07 11.00.00.000000000 PM
02-MAY-07 07.00.00.000000000 AM
+000000000 08:00:00.000000000
01-MAY-07 10.00.00.000000000 AM
02-MAY-07 09.00.00.000000000 AM
+000000000 23:00:00.000000000
Tu-Oanh, May 28, 2007 - 5:55 am UTC
That was exactly what I am after. Thank you Tom for the quick response.
Business Works and Ticket Hours
Lalitha, June 29, 2010 - 1:15 pm UTC
Mr.Tom
Thanks for your excellent SITE. Is this feasible,
I need to calculate the time difference in hours and minutes excluding Weekends(Saturday,Sunday) and also Excluding non Business Hours (6:01pm to 8:59 am) at a given point of time
It is some like below
Create table ticket_details(Ticket_ID Number,Ticket_Intime Date);
insert into ticket_details values (100,to_date('25/06/2010 15:00:00','DD/MM/YYYY HH24:MI:SS'));
insert into ticket_details values (101,to_date('28/06/2010 10:20:19','DD/MM/YYYY HH24:MI:SS'));
commit;
Sample:-
If i see the status on 28-Jun-2010 at 5:30 pm the ticket age = 11:30 (That is 25th (3pm to 6pm =3 Hrs) + 28th (9am to 5:30 pm) = 3+8:30 = ANS = 11:30)
Expected Output on 28-Jun-2010 at 5:30 pm
Ticket_ID Ticket_Intime Ticket_Age
100 25/06/2010 15:00:00 11:30
101 28/06/2010 10:20:19 07:10
Another Sample:-
If i see the status on 29-Jun-2010 at 7:30pm the ticket age = 22:30 Hrs (That is 25th (3pm to 6pm =3 Hrs) + 28th (9am-6pm = 9 hrs) + 29th(9am - 7:30 pm = 10:30 Hrs) = 3+9+10:30 = ANS = 22:30 Hrs)
Expected Output on 29-Jun-2010 at 7:30 pm
Ticket_ID Ticket_Intime Ticket_Age
100 25/06/2010 15:00:00 22:30
101 28/06/2010 10:20:19 18:10
Is this feasible in a single sql to see the ticket age of all tickets.
Regards
Lalitha
Business Works and Ticket Hours
Lalitha, July 11, 2010 - 7:55 pm UTC
Mr.Tom
Is the above date calculation feasible ?.
Thanks for your time.
Regards
Lalitha
July 19, 2010 - 10:19 am UTC
ops$tkyte%ORA10GR2> alter session set nls_date_format = 'dy dd-mon hh:mi:ssam';
Session altered.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> with dt
2 as
3 (select to_date( '28-jun-2010 5:30pm', 'dd-mon-yyyy hh:miam' ) thedate from dual)
4 select ticket_id, ticket_intime, st_time, end_time, end_time-st_time ela
5 from (
6 select ticket_id, ticket_intime,
7 greatest( ticket_intime, trunc(ticket_intime+x.column_value)+9/24 ) st_time,
8 least( trunc(ticket_intime+x.column_value)+18/24, dt.thedate ) end_time
9 from ticket_details dtls, dt,
10 table( cast( multiset( select rownum-1
11 from dt
12 connect by level <=
13 trunc(dt.thedate)-trunc(dtls.ticket_intime)+1
14 ) as sys.odciNumberList
15 )
16 ) x
17 where to_char(ticket_intime+x.column_value,'dy') not in ('sat','sun')
18 )
19 /
TICKET_ID TICKET_INTIME ST_TIME END_TIME ELA
---------- --------------------- --------------------- --------------------- ----------
100 fri 25-jun 03:00:00pm fri 25-jun 03:00:00pm fri 25-jun 06:00:00pm .125
100 fri 25-jun 03:00:00pm mon 28-jun 09:00:00am mon 28-jun 05:30:00pm .354166667
101 mon 28-jun 10:20:19am mon 28-jun 10:20:19am mon 28-jun 05:30:00pm .298391204
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> with dt
2 as
3 (select to_date( '28-jun-2010 5:30pm', 'dd-mon-yyyy hh:miam' ) thedate from dual)
4 select ticket_id, ticket_intime,
5 trunc(sum(end_time-st_time)*24) hours,
6 mod( sum(end_time-st_time)*24*60, 60 ) mins
7 from (
8 select ticket_id, ticket_intime,
9 greatest( ticket_intime, trunc(ticket_intime+x.column_value)+9/24 ) st_time,
10 least( trunc(ticket_intime+x.column_value)+18/24, dt.thedate ) end_time
11 from ticket_details dtls, dt,
12 table( cast( multiset( select rownum-1
13 from dt
14 connect by level <=
15 trunc(dt.thedate)-trunc(dtls.ticket_intime)+1
16 ) as sys.odciNumberList
17 )
18 ) x
19 where to_char(ticket_intime+x.column_value,'dy') not in ('sat','sun')
20 )
21 group by ticket_id, ticket_intime
22 order by ticket_id, ticket_intime
23 /
TICKET_ID TICKET_INTIME HOURS MINS
---------- --------------------- ---------- ----------
100 fri 25-jun 03:00:00pm 11 30
101 mon 28-jun 10:20:19am 7 9.68333333
Business Works and Ticket Hours
V.Hariharaputhran, July 19, 2010 - 1:11 pm UTC
Mr.Oracle,
Just trying to understand the above SQL,
1. 9/24 & 18/24 are for 9am and 6 pm timelines.
2. table( cast( multiset( ....)sys.odciNumberList))x - Is used to inorder to use the columns in the table list within its own query just like using dtls.ticket_intime, even though dtls is not part of this connect by level query, and yes if we use like below
select rownum-1 from dt,ticket_details dtls connect by level <= trunc(dt.thedate)-trunc(dtls.ticket_intime)+1
It would lead to cartesian product.
Last but not least- Excellent, appreciate your patience reply, and the way you deal, approach and provide a solution is simply superb GURU.
Regards
V.Hari
July 19, 2010 - 2:16 pm UTC
1) yes.
2) it is used to generate a set of rows - not to use the columns in a table list. The table() subquery is always "joined to correctly", there is never a cartesian table.
Business Works and Ticket Hours
V.Hariharaputhran, July 19, 2010 - 2:32 pm UTC
Mr.Oracle,
Thanks for your reply,
Yes it is for generating the rows, what i actually meant is
Feasible-
select ticket_id, ticket_intime,
greatest( ticket_intime, trunc(ticket_intime+x.column_value)+9/24 ) st_time,
least( trunc(ticket_intime+x.column_value)+18/24, dt.thedate ) end_time
from ticket_details dtls, dt,
table( cast( multiset( select rownum-1
from dt
connect by level <=
trunc(dt.thedate)-trunc(dtls.ticket_intime)+1
) as sys.odciNumberList
)
) x
NOT-Feasible- - As dtls.ticket_intime in the connect by level where clause becomes invalid identifier.
select ticket_id, ticket_intime,
greatest( ticket_intime, trunc(ticket_intime+x.column_value)+9/24 ) st_time,
least( trunc(ticket_intime+x.column_value)+18/24, dt.thedate ) end_time
from ticket_details dtls, dt,
( select rownum-1
from dt
connect by level <=
trunc(dt.thedate)-trunc(dtls.ticket_intime)+1
) x
Thus Table(cast helps me to use a column from a table which i just included in the table list(ticket_details dtls, dt)
Regards
V.Hari
July 23, 2010 - 6:34 am UTC
right - you cannot join to a correlated subquery, you can use a TABLE expression to generate a set (in reality you are unnesting a nested table) for each row.
Add time to date field
V.Hariharaputhran, July 23, 2010 - 7:19 am UTC
Thanks for your time MR.Oracle, I feel like, you take SQL as a brisk morning coffee.
Regards
V.Hari