Re: help with my sql

From: charlie.peltier <here-antispam)_at_verizon.net>
Date: Thu, 22 Aug 2002 11:56:09 GMT
Message-ID: <tj499.11442$CW1.8207@nwrddc02.gnilink.net>

Sorry, none seem to work.
For your convinence, let me put all the table here, so you can create the table and try it.
SQL> create table A (date_key number, polltime date); Table created.
SQL> create table B

(date_key number,year varchar2(4),month varchar2(2),day varchar2(2),
    hour varchar2(2), minute varchar2(2));

Table created.
SQL> insert into A (polltime) values
 (to_date('2002/08/20 23:59:59','yyyy/mm/dd hh24:mi:ss'));

SQL>
    insert into A (polltime) values
(to_date('2002/08/20 23:59:59','yyyy/mm/dd hh24:mi:ss'));

1 row created.

SQL>
 insert into A (polltime) values
  (to_date('2002/08/20 22:59:59','yyyy/mm/dd hh24:mi:ss'));

1 row created.

SQL>
    insert into A (polltime) values
(to_date('2002/08/20 21:59:59','yyyy/mm/dd hh24:mi:ss'));

1 row created.

SQL>insert into B
select distinct
1,

to_char(polltime,'yyyy'),
to_char(polltime,'mm'),
to_char(polltime,'dd'),
to_char(polltime,'hh24'),
to_char(polltime,'mi') from A;

3 rows created.
SQL> update B set date_key =2 where hour=22;

1 row updated.
SQL> update B set date_key =3 where hour=23;

1 row updated.

SQL> select date_key,to_char(polltime,'yyyy/mm/dd hh24:mi:ss') from a;

  DATE_KEY TO_CHAR(POLLTIME,'Y

---------- -------------------
           2002/08/20 23:59:59
           2002/08/20 23:59:59
           2002/08/20 22:59:59
           2002/08/20 21:59:59

4 rows selected.

SQL> select * from b;

  DATE_KEY YEAR MO DA HO MI
---------- ---- -- -- -- --

         1 2002 08 20 21 59
         2 2002 08 20 22 59
         3 2002 08 20 23 59

3 rows selected.

what I need to have is
 DATE_KEY TO_CHAR(POLLTIME,'Y

---------- -------------------
    3       2002/08/20 23:59:59
    3       2002/08/20 23:59:59
    2       2002/08/20 22:59:59
    1      2002/08/20 21:59:59

Could somebody help me out?

Thanks Received on Thu Aug 22 2002 - 06:56:09 CDT