Re: query requirement
- From: Tim Gorman <tim@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 17 May 2005 10:49:16 -0600
Oh what fun!
Some time ago, based on another thread on ORACLE-L, I posted a pipelined
table function named BETWIXT. I've also posted it at
"http://www.EvDBT.com/tools.htm";; look for "betwixt.sql". It=B9s a really
simple function and I=B9ve posted a similar one called INFINITE_DUAL (same
page, script =B3infinite_dual.sql=B2), based on another discussion on this list
last year.
Pipelined table functions require Oracle9i and above, and the BETWIXT
function accepts two dates (representing a date range) as parameters. It
returns all time intervals within that range, with the time interval
defaulting to 1 day, but it can be specified as the third parameter.
So, to get all of the seconds between "now" and "one minute from now", we
specify SYSDATE for the begin-date and SYSDATE+(1/1440) as the end-date,
with the fraction (1/86400) representing intervals of one second...
SQL> alter session set nls_date_format =3D =8CDD-MON-YYYY HH24:MI:SS=B9;
SQL>=20
SQL> select * from table(betwixt(sysdate, sysdate+(1/1440),(1/86400)));
=20
DT
--------------------
17-MAY-2005 10:24:07
17-MAY-2005 10:24:08
17-MAY-2005 10:24:09
17-MAY-2005 10:24:10
17-MAY-2005 10:24:11
17-MAY-2005 10:24:12
17-MAY-2005 10:24:13
17-MAY-2005 10:24:14
17-MAY-2005 10:24:15
17-MAY-2005 10:24:16
17-MAY-2005 10:24:17
17-MAY-2005 10:24:18
17-MAY-2005 10:24:19
17-MAY-2005 10:24:20
17-MAY-2005 10:24:21
17-MAY-2005 10:24:22
17-MAY-2005 10:24:23
17-MAY-2005 10:24:24
17-MAY-2005 10:24:25
17-MAY-2005 10:24:26
17-MAY-2005 10:24:27
17-MAY-2005 10:24:28
17-MAY-2005 10:24:29
17-MAY-2005 10:24:30
17-MAY-2005 10:24:31
17-MAY-2005 10:24:32
17-MAY-2005 10:24:33
17-MAY-2005 10:24:34
17-MAY-2005 10:24:35
17-MAY-2005 10:24:36
17-MAY-2005 10:24:37
17-MAY-2005 10:24:38
17-MAY-2005 10:24:39
17-MAY-2005 10:24:40
17-MAY-2005 10:24:41
17-MAY-2005 10:24:42
17-MAY-2005 10:24:43
17-MAY-2005 10:24:44
17-MAY-2005 10:24:45
17-MAY-2005 10:24:46
17-MAY-2005 10:24:47
17-MAY-2005 10:24:48
17-MAY-2005 10:24:49
17-MAY-2005 10:24:50
17-MAY-2005 10:24:51
17-MAY-2005 10:24:52
17-MAY-2005 10:24:53
17-MAY-2005 10:24:54
17-MAY-2005 10:24:55
17-MAY-2005 10:24:56
17-MAY-2005 10:24:57
17-MAY-2005 10:24:58
17-MAY-2005 10:24:59
17-MAY-2005 10:25:00
17-MAY-2005 10:25:01
17-MAY-2005 10:25:02
17-MAY-2005 10:25:03
17-MAY-2005 10:25:04
17-MAY-2005 10:25:05
17-MAY-2005 10:25:06
=20
60 rows selected.
So, now you can use the TABLE(BETWIXT) function in the FROM clause of your
query and outer-join from the DT values returned from BETWIXT to the values
in your table. Where ever your outer-join fails, you can put the phrase =B3N=
o
record in this second=B2 or something like that:
SQL> select * from test_test;
=20
XDATE
--------------------
17-MAY-2005 10:35:37
=20
1 row selected.
=20
SQL> select x.dt,
2 nvl(to_char(y.xdate, 'DD-MON-YYYY HH24:MI:SS'),
3 'No record for this second') xdate
4 from table(betwixt(sysdate, sysdate+(1/1440),(1/86400))) x,
5 test_test y
6 where x.dt =3D y.xdate (+);
=20
DT XDATE
-------------------- -------------------------
17-MAY-2005 10:34:47 No record for this second
17-MAY-2005 10:34:48 No record for this second
17-MAY-2005 10:34:49 No record for this second
17-MAY-2005 10:34:50 No record for this second
17-MAY-2005 10:34:51 No record for this second
17-MAY-2005 10:34:52 No record for this second
17-MAY-2005 10:34:53 No record for this second
17-MAY-2005 10:34:54 No record for this second
17-MAY-2005 10:34:55 No record for this second
17-MAY-2005 10:34:56 No record for this second
17-MAY-2005 10:34:57 No record for this second
17-MAY-2005 10:34:58 No record for this second
17-MAY-2005 10:34:59 No record for this second
17-MAY-2005 10:35:00 No record for this second
17-MAY-2005 10:35:01 No record for this second
17-MAY-2005 10:35:02 No record for this second
17-MAY-2005 10:35:03 No record for this second
17-MAY-2005 10:35:04 No record for this second
17-MAY-2005 10:35:05 No record for this second
17-MAY-2005 10:35:06 No record for this second
17-MAY-2005 10:35:07 No record for this second
17-MAY-2005 10:35:08 No record for this second
17-MAY-2005 10:35:09 No record for this second
17-MAY-2005 10:35:10 No record for this second
17-MAY-2005 10:35:11 No record for this second
17-MAY-2005 10:35:12 No record for this second
17-MAY-2005 10:35:13 No record for this second
17-MAY-2005 10:35:14 No record for this second
17-MAY-2005 10:35:15 No record for this second
17-MAY-2005 10:35:16 No record for this second
17-MAY-2005 10:35:17 No record for this second
17-MAY-2005 10:35:18 No record for this second
17-MAY-2005 10:35:19 No record for this second
17-MAY-2005 10:35:20 No record for this second
17-MAY-2005 10:35:21 No record for this second
17-MAY-2005 10:35:22 No record for this second
17-MAY-2005 10:35:23 No record for this second
17-MAY-2005 10:35:24 No record for this second
17-MAY-2005 10:35:25 No record for this second
17-MAY-2005 10:35:26 No record for this second
17-MAY-2005 10:35:27 No record for this second
17-MAY-2005 10:35:28 No record for this second
17-MAY-2005 10:35:29 No record for this second
17-MAY-2005 10:35:30 No record for this second
17-MAY-2005 10:35:31 No record for this second
17-MAY-2005 10:35:32 No record for this second
17-MAY-2005 10:35:33 No record for this second
17-MAY-2005 10:35:34 No record for this second
17-MAY-2005 10:35:35 No record for this second
17-MAY-2005 10:35:36 No record for this second
17-MAY-2005 10:35:37 17-MAY-2005 10:35:37
17-MAY-2005 10:35:38 No record for this second
17-MAY-2005 10:35:39 No record for this second
17-MAY-2005 10:35:40 No record for this second
17-MAY-2005 10:35:41 No record for this second
17-MAY-2005 10:35:42 No record for this second
17-MAY-2005 10:35:43 No record for this second
17-MAY-2005 10:35:44 No record for this second
17-MAY-2005 10:35:45 No record for this second
17-MAY-2005 10:35:46 No record for this second
=20
60 rows selected.
Hope this helps...
-Tim
on 5/17/05 6:31 AM, The Human Fly at sjaffarhussain@xxxxxxxxx wrote:
> Good Day everyone!
>=20
> We have one online table which gets a lot of insertions every second
> and there could no insertions sometimes, This table has one timestamp
> column which stores timestamp when an inserts take places.
> My requirements is, say there are insertions at 10:10:10 AM and the
> next insertion is at 10:11:10AM, i.e. there are no transactions take
> place from 10:10 to :10:11 am. When I use normal query which only
> display records which are exists for the stored timestamp.
> Is it possible that I write a query which see the next record and
> between timestamp is not 1 second says, no rocrd in this second.
>=20
> Thanking you in advance for your valuable time.
>=20
> Best Regards,
> Jaffar, OCP DBA
> Banque Saudi Fransi
> Saudi Arabia
> ----------------------------------------------------------------------
> "It is your atittude, not your aptitude that determins your altitude."
--
//www.freelists.org/webpage/oracle-l
Other related posts:
