?
Solved

Oracle Update column based on an expression

Posted on 2014-10-04
6
Medium Priority
?
283 Views
Last Modified: 2014-10-04
I have a table which has a calculated VARCHAR2 column based on an event date. Yes, I know that this is not advisable.
age_group is a VARCHAR2 column.
start_date is a DATE column.

I need to set the value of the age_group  column.

Psuedocode: Set the value of Table1.age_group  equal to 'LT8'
where the number of days since the start date is between 0 and 7.

UPDATE Table1
SET age_group = ‘LT8’  
WHERE  Table1 .(SysDate-start_date)  BETWEEN 0 AND 7 ;

Is this the correct syntax?  Does (SysDate - start-date) return a number of days, or do I need to apply a conversion function?

Thanks,
0
Comment
Question by:Dovberman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40361363
UPDATE Table1
SET age_group = ‘LT8’  
WHERE SysDate- to_Date(start_date,'yyyy-mm-dd')  BETWEEN 0 AND 7 ;

before you update first test the records affected by running :

select start_date, SysDate- to_Date(start_date,'yyyy-mm-dd')   as diff
from Table1

WHERE SysDate- to_Date(start_date,'yyyy-mm-dd')  BETWEEN 0 AND 7 ;
0
 

Author Comment

by:Dovberman
ID: 40361382
Thanks,

This is part of a procedure.
Will a count work? I need a count for each of 9 date different groups.

SELECT Count(start_date) as Group1 WHERE SysDate- to_Date(start_date,'yyyy-mm-dd')  BETWEEN 0 AND 7,
 SELECT Count(start_date) as Group2 WHERE SysDate- to_Date(start_date,'yyyy-mm-dd')  BETWEEN 8 AND 30 ;

Returns:
Group1       Group2    ...

Thanks,
27                56
0
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 2000 total points
ID: 40361399
try this, you just need to add additional case statements and sums

select suM(group1) as group1, sum(group2) as group2
from(
SELECT 
case when SysDate- to_Date(start_date,'yyyy-mm-dd')  BETWEEN 0 AND 7 then 1 else 0 end as group1,
case when SysDate- to_Date(start_date,'yyyy-mm-dd')   BETWEEN 8 AND 30 then 1 else 0 end as group2
from table1
)a

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Dovberman
ID: 40361406
Just what I was looking for.
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40361407
Glad that I helped ;)
0
 

Author Closing Comment

by:Dovberman
ID: 40361409
Just what I was looking for.

Thanks
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question