Re: Table joins

From: Michel Cadot <micadot_at_netcourrier.com>
Date: 2000/06/06
Message-ID: <8hid7j$so3$1@s2.feed.news.oleane.net>#1/1

I have try with Oracle 7.3.4 on NT.
It seems to work fine:

v734> create table tablea (model varchar2(10), code integer primary key); v734> create table tableb (timestamp date, code integer references tablea,

  2                        value integer,
  3                        primary key (code, timestamp));
v734> insert into tablea values ('ACME',1001);

v734> insert into tablea values ('RR',1100); v734> insert into tableb values (to_date('Jan/4/2000 23:30','Mon/DD/YYYY HH24:MI'),

  2                             1100, 10);
v734> insert into tableb values (to_date('Jan/5/2000 23:50','Mon/DD/YYYY HH24:MI'),
  2                             1100, 11);
v734> insert into tableb values (to_date('Jan/5/2000 21:00','Mon/DD/YYYY HH24:MI'),
  2                             1001, 10);
v734> insert into tableb values (to_date('Jan/5/2000 23:50','Mon/DD/YYYY HH24:MI'),
  2                             1001, 15);
v734> commit;

v734> set timing on
v734> select m.model, sum(s.value) VAL
  2 from TableA m, TableB s
  3 where m.code = s.code
  4 and s.TimeStamp between

  5            to_date( 'Jan/4/2000 18:00', 'Mon/DD/YYYY HH24:MI')
  6        and to_date( 'Jan/5/2000 3:00', 'Mon/DD/YYYY HH24:MI')

  7 Group by m.model
  8 order by VAL desc;

MODEL VAL
---------- ----------

RR                 10

1 row selected.

 real: 60

v734> select m.model, sum(s.value) VAL
  2 from TableA m, TableB s
  3 where m.code = s.code
  4 and s.TimeStamp between

  5            to_date( 'Jan/4/2000 18:00', 'Mon/DD/YYYY HH24:MI')
  6        and to_date( 'Jan/6/2000 3:00', 'Mon/DD/YYYY HH24:MI')

  7 Group by m.model
  8 order by VAL desc;

MODEL VAL
---------- ----------

ACME               25
RR                 21

2 rows selected.

 real: 80

--
Have a nice day
Michel


JagMan <jagman98_at_home.com> a écrit dans le message : 393C3D66.760B01CE_at_home.com...


> The Select command does not generate any output.

>

> Follwing example:

>

> TableA

>

>     Model        Code

>     ACME        1001

>     RR             1100

>

> >Index Code;

>

> TableB

>     TimeStamp                   Code         VALUE

>     Jan/4/2000 23:30        1100                10

>     Jan/5/2000 23:50        1100                11

>     Jan/5/2000 21:00        1001                10

>     Jan/5/2000 23:50        1001                15

>

> >Index TimeStamp, and Code

>

> Output:

>

> Time Range [ Jan/4/2000 18:00 ] - [ Jan/5/2000 03:00 ]

>

>     Model        Total

>     ACNE            25

>     RR                21

>

> ============================

>

> select m.model, sum(s.value) VAL from TableA m, TableB s where m.code =

> s.code and s.TimeStampe between to_date( 'Jan/4/2000 18:00',

> 'Mon/DD/YYYY HH24:MI') and to_date(  'Jan/5/2000 3:00', 'Mon/DD/YYYY

> HH24:MI') Group by m.model order by VAL desc;

>

> The command seems to hang at prompt.

>

> Thanks in Advance!

>

>

Received on Tue Jun 06 2000 - 00:00:00 CDT