Re: help with my sql

From: <Kenneth>
Date: Thu, 22 Aug 2002 12:40:59 GMT
Message-ID: <3d64d9ff.16911607@news.capgemini.se>

Hi Charlie,

Apparently, my assumption of date_key being unique in table A did not hold.

So we must use polltime instead. We also need to truncate away the seconds from polltime when joining with B, as B does not hold seconds :

update A
  set A.date_key = (select date_key

                    from b
                   where B.year = to_char(A.polltime,'yyyy')
                     and B.month = to_char(A.polltime,'mm')
                     and B.day = to_char(A.polltime,'dd')
                     and B.hour = to_char(A.polltime,'hh24')
                     and B.minute = to_char(A.polltime,'mi'))

where trunc(polltime,'MI') = ( select to_date(year || month || day || hour || minute,'YYYYMMDDHH24MI') from b where

                       b.year = to_char(A.polltime,'yyyy')
                     and b.month = to_char(A.polltime,'mm')
                     and b.day = to_char(A.polltime,'dd')
                     and b.hour = to_char(A.polltime,'hh24')
                     and b.minute = to_char(A.polltime,'mi'));		

On Thu, 22 Aug 2002 11:56:09 GMT, "charlie.peltier" <charlie.peltier(delete here-antispam)@verizon.net> wrote:

>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 - 07:40:59 CDT