Home » SQL & PL/SQL » SQL & PL/SQL » Updating dynamically depending on values
Updating dynamically depending on values [message #233305] Wed, 25 April 2007 10:46 Go to next message
bigove
Messages: 4
Registered: April 2007
Junior Member
Hi all,

I need to update a column depending on how many hours since the last data load, giving a new value depending on the result. Basically the values are from 1-12, for hours starting from 0, i.e. if the time difference between the contacttime (a current column) and the current time is between 0-1 hours then the value should be 1, 2-3 should be 2, 4-5 should be 3, and so on.

After some help on these boards my statement picks out the correct values:

select 
    CASE 
              ( case 
                    when (to_number(to_char(sysdate, 'HH24')) - to_number(substr(CONTACTTIME, 1, 2)) < 0 )
                        then (to_number(to_char(sysdate, 'HH24')) - to_number(substr(CONTACTTIME, 1, 2)) + 24)
                    else (to_number(to_char(sysdate, 'HH24')) - to_number(substr(CONTACTTIME, 1, 2)))
                end )
       when 0 then 1
       when 1 then 1
       when 2 then 2
       when 3 then 2
       when 4 then 3
       when 5 then 3
       when 6 then 4
       when 7 then 4
       when 8 then 5
       when 9 then 5
       when 10 then 6
       when 11 then 6
       when 12 then 7
       when 13 then 7
       when 14 then 8
       when 15 then 8
       when 16 then 9
       when 17 then 9
       when 18 then 10
       when 19 then 10
       when 20 then 11
       when 21 then 11
       ELSE 12
    END new_value
from scho.wcontactlogging;


My question is, how would I then translate that to an update statement? Any help/suggestions much appreciated.
Re: Updating dynamically depending on values [message #233308 is a reply to message #233305] Wed, 25 April 2007 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
update scho.wcontactlogging
set mycol = case ... end
/

Regards
Michel
Re: Updating dynamically depending on values [message #233310 is a reply to message #233305] Wed, 25 April 2007 11:04 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Why do you store the contact time as a varchar2. A date column would have been MUCH better (and simpler) and you could have calculated it with a simple formula

FLOOR(((SYSDATE - CONTACTTIME)* 24)/2)+1
Re: Updating dynamically depending on values [message #233712 is a reply to message #233305] Fri, 27 April 2007 04:01 Go to previous message
bigove
Messages: 4
Registered: April 2007
Junior Member
Thanks for your help here Michel, that looks to do the job.

As for the datatype used; this was created before I arrived on the project so I didn't have any input. It is something to be looked at, but it was a case of writing the script taking priority!
Previous Topic: imp and exp in oracle
Next Topic: Error in Order By SQL STMT
Goto Forum:
  


Current Time: Thu Dec 08 19:52:42 CST 2016

Total time taken to generate the page: 0.09721 seconds