sqldatetimecaseinformix

Informix - querying for latest date across multiple columns and multiple rows


I need to get the latest date from multiple database columns (date_entered, date_updated) in an Informix database. Logically, date_updated should always be the latest but that is not true in this data so I have to check which is latest. Both columns also support and contain nulls. Both are null in several cases, so I have to accommodate a default "low" date for those also.

On top of that, I also need to get the latest result across n records that share the same group number.

I've written the following SQL to do this:

SELECT  group_number, record_number, date_entered, date_updated
     , (SELECT MAX(CASE
        WHEN sq1.date_updated IS NOT NULL AND sq1.date_updated>=sq1.date_entered THEN sq1.date_updated
        WHEN sq1.date_entered IS NOT NULL THEN sq1.date_entered
        --ELSE TO_DATE('1950-01-01 00:00', '%Y-%m-%d %H:%M')
        END) FROM reports sq1 WHERE sq1.group_number=reports.group_number) AS date_latestupdate
FROM reports

This is what I would expect to get, and works for cases where both dates are not null:

group_number record_number date_entered date_updated date_latestupdate
4972 1 2007-06-20 11:59:00.0 2007-06-22 13:25:15.0 2007-07-26 20:53:15.0
4972 2 2007-06-21 11:59:00.0 2007-07-26 20:53:15.0 2007-07-26 20:53:15.0
4972 3 2007-06-22 11:59:00.0 2007-06-26 19:40:15.0 2007-07-26 20:53:15.0

However, the ELSE component of the case statement is commented out and would fail to provide a date if both columns are null. If I uncomment it I get the following:

Error: Corresponding types must be compatible in CASE expression.
SQLState: IX000
ErrorCode: -800

The date_entered and date_updated columns are of type datetime, and the Informix documentation says that TO_DATE returns a datetime value (https://www.ibm.com/docs/en/informix-servers/12.10?topic=dcf-date-function-1). If I query the to_date function in there on its own, it appears to generate a datetime value.

I tried looking for other means of converting, casting, etc. the datetime value, but the only thing I could find for Informix to force the data type is the TO_DATE function.


Solution

  • In the text description of the -800 error it is mentioned that all the result values of the WHEN clauses should be compatible data types, and this includes the ELSE clause. Although the data type of the TO_DATE function is also a datetime, it is not of the same precision, or size, as the WHEN clauses. The TO_DATE function will return the maximum precision which is YEAR to FRACTION(5). If you cast the result of the TO_DATE function so as to match the WHEN clauses, it should then work:

    ...
    ELSE TO_DATE('1950-01-01 00:00', '%Y-%m-%d %H:%M')::datetime year to second