I have a COALESCE function as follows:

COALESCE(date1, date2, date3) 

However, because of an upstream process I do not control, "date2" NULLs were transformed into '1900-01-01'

My goal is to: SELECT date1, if date1 null select date2, but only if date2 is not '1900-01-01' - otherwise select date3.

What is the best way to write this? I want to avoid a clunky CASE WHEN statement if possible

CodePudding user response：

Like I said in the comment, really the data needs fixing. I realise you state that this is upstream, but if this is a problem then feedback upstream that it's a problem and get it fixed. Don't use arbitrary values for values that should be NULL; use NULL.

For the data as you have it at the moment, you can use NULLIF to return NULL is the value is equal to another. If this is just for date2 then you would do the following:

COALESCE(date1, NULLIF(date2,'19000101'), date3)