Max date between 3 fields

September 4, 2024

need some help with how to code this logic:

We have a PL/SQL block where there is 3 date fields retrieved from 3 different tables. For simplicity:

v_registration_date
v_optout_date
v_dormancy_date

I need to know if more than one of those dates are populated with actual date values, which one has the MAX value. This is easily done by using GREATEST. But what I also need to know is which field it is, because depending on that, different actions will need to be taken.

So, if 2 or 3 of those fields actually contain actual dates, what the MAX value is, and which field contained that max date. Not sure if there is a way to add a tag or something similar within the GREATEST command…….

Any thoughts?

>Solution :

Just use IF, THEN, ELSE or CASE. E.g.:

v_newest_date := GREATEST(v_registration_date, v_optout_date, v_dormancy_date);

CASE v_newest_date
  WHEN v_dormancy_date     THEN v_status := 'dormancy';
  WHEN v_optout_date       THEN v_status := 'optout';
  WHEN v_registration_date THEN v_status := 'registration';
END CASE;