Solved

Oracle Update column based on an expression

Posted on 2014-10-04
6
280 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
  • 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 500 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

726 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