CODEWITHSUNDEEP
sqloracle-databaseteradata
Adam
Adam

Reputation: 2465

Choosing the Most Recent Date

I have three columns (date type) in a table, and I would like to make a fourth column which needs to contain the latest date of the first three columns. Additionally, first three columns can have NULL values. Is there any special function in Teradata or some elegant approach on how to solve this, other than using CASE statement?

Upvotes: 1

Views: 1887

Answers (2)

dnoeth
dnoeth

Reputation: 60513

There's a GREATEST function, but it will return NULL if any of the dates are NULL.

As a workaround you can COALESCE the NULLs with an unused date. If all three columns are NULL you can finally apply NULLIF:

NULLIF(GREATEST(COALESCE(col1,DATE '0001-01-01')
               ,COALESCE(col2,DATE '0001-01-01')
               ,COALESCE(col3,DATE '0001-01-01')
               )
      , DATE '0001-01-01'
      )

Teradata supports the GREATEST function in TD14, but due to strange reasons it's not defined for DATEs :-(

But there's a workaround:

CAST(NULLIF(GREATEST(COALESCE(CAST(col1 AS INT),-1)
           ,COALESCE(CAST(col2 AS INT),-1)                          
           ,COALESCE(CAST(col3 AS INT),-1)                          
           )
    ,-1) AS DATE)

Btw, the CASE approach is not that bad, each WHEN needs one column less:

CASE
   WHEN COALESCE(col1, DATE '0001-01-01') >= COALESCE(col2, DATE '0001-01-01')
    AND COALESCE(col1, DATE '0001-01-01') >= COALESCE(col3, DATE '0001-01-01')
   THEN col1
   WHEN COALESCE(col2, DATE '0001-01-01') >= COALESCE(col3, DATE '0001-01-01')
   THEN col2
   ELSE col3
END

Upvotes: 3

Alex Poole
Alex Poole

Reputation: 191570

If you're on 11g you could use unpivot to convert the cols into individual rows and then aggregate over those. Not sure this would count as 'elegant', but it doesn't use case...

select id, col1, col2, col3, max(unpivot_col) as col4
from (
  select *
  from (
    select id, col1, col2, col3,
      col1 as x_col1, col2 as x_col2, col3 as x_col3
    from t42
  )
  unpivot include nulls (unpivot_col for col in (x_col1, x_col2, x_col3))
)
group by id, col1, col2, col3
order by id;

SQL Fiddle with intermediate steps.

The inner query creates extra columns which are used for the unpivot because I think you want to also show the original three values. If you don't then you can just select directly from your tables and unpivot on col etc.

And the include nulls clause means it shows a row even if all three original columns are null. If that can never happen, or you don't want to show then if it does, then you coudl leave that out.

Upvotes: 0

Related Questions