Bad timestamp external representation Error in Netezza

Question

I was running below query in Netezza and getting mentioned error.

Query:

SELECT * FROM WORKORDER SRC  
    INNER JOIN  APPOINTMENT TRG
        ON SRC.ACCESSID = TRG.SEEKER_ID 
        AND (COALESCE(SRC.SLAEXPIRY, '') = COALESCE(TRG.SLA_EXPIRY_DATE_TS, ''))

Error:

ERROR [HY000] ERROR:  Bad timestamp external representation ''

As I can see NULL values sometimes for SLAEXPIRY and SLA_EXPIRY_DATE_TS columns tried following option, no luck though

SELECT * FROM WORKORDER SRC  
    INNER JOIN  APPOINTMENT TRG
        ON SRC.ACCESSID = TRG.SEEKER_ID 
        AND case when COALESCE(SRC.SLAEXPIRY, '') is not null then COALESCE(SRC.SLAEXPIRY, '')
                            else NULL end = case when COALESCE(TRG.SLA_EXPIRY_DATE_TS, '') is not null then COALESCE(TRG.SLA_EXPIRY_DATE_TS, '')
                                            else NULL end

Answer 1

The fundamental issue here is that the empty string (i.e. '') cannot be cast to a timestamp.

TESTDB.ADMIN(ADMIN)=> select coalesce(current_timestamp, '');
ERROR:  Bad timestamp external representation ''

The secondary issue, of course, is that NULLs don't JOIN. The following query should work for you if you would like to JOIN the NULLs anyway. Don't expect great performance from this expression based join.

SELECT *
FROM WORKORDER SRC
   INNER JOIN APPOINTMENT TRG
   ON SRC.ACCESSID = TRG.SEEKER_ID
   AND
      (
         (
            SRC.SLAEXPIRY = TRG.SLA_EXPIRY_DATE_TS
         )
      OR
         (
            SRC.SLAEXPIRY           IS NULL
         AND TRG.SLA_EXPIRY_DATE_TS IS NULL
         )
      )