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 only takes a minute to sign up.
Sign up to join this community
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.
It sounds like you want the GREATEST function
INSERT INTO my_table( date_column )
VALUES( GREATEST( date_default, date_insert_value ));
GREATEST? Went straight for CASE :)
CASE may be preferred when there are Nulls. GREATEST(1, NULL) results in NULL and not 1.
Feb 14, 2013 at 15:56
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;
