Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to create an insert statement in a PL/SQL Script that inserts the larger of two different values. Conceptually, what I want to do is something like

Insert into MYTABLE (DATE_INSERT)
VALUE (MAX(DATE_DEFAULT, DATE_INSERT_VALUE))

where DATE_DEFAULT and DATE_INSERT_VALUE are local variables of type DATE. The MAX function, however, is an aggregate function so I can't pass two arguments to it.

share|improve this question
    
Do you really mean PL/SQL? That is for stored procedures/functions triggers. Regular scripts are "only" SQL –  a_horse_with_no_name Feb 14 '13 at 16:08
add comment

2 Answers

up vote 5 down vote accepted

It sounds like you want the GREATEST function

INSERT INTO my_table( date_column )
 VALUES( GREATEST( date_default, date_insert_value ));
share|improve this answer
    
Haha, how could I have forgotten about GREATEST? Went straight for CASE :) –  FreshPhilOfSO Feb 14 '13 at 15:21
1  
CASE may be preferred when there are Nulls. GREATEST(1, NULL) results in NULL and not 1. –  ypercube Feb 14 '13 at 15:56
add comment

Your question isn't 100% clear, but if you just want to insert the larger of two dates you can use a case statement (there are lots of other ways of doing this too!):

with data as (
  select DATE'2010-01-01' as DATE_INSERT_VALUE, 
         DATE'2010-02-03' as DATE_DEFAULT
  from DUAL
)
select case when DATE_INSERT_VALUE > DATE_DEFAULT then DATE_INSERT_VALUE else DATE_DEFAULT end case 
from data;
share|improve this answer
    
Lalala. They're next to each other on the keyboard :P –  FreshPhilOfSO Feb 14 '13 at 15:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.