Re: query requirement

From: Tim Gorman <tim_at_evdbt.com>
Date: Tue, 17 May 2005 10:49:16 -0600
Message-ID: <BEAF7CAC.276EE%tim@evdbt.com>

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_at_gmail.com 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."

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 17 2005 - 12:54:03 CDT