3

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.

  • 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
9

It sounds like you want the GREATEST function

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

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;
| improve this answer | |
  • Lalala. They're next to each other on the keyboard :P – Philᵀᴹ Feb 14 '13 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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