sql - Netezza 中错误的时间戳外部表示错误

Question

我在 Netezza 中运行以下查询并收到提到的错误。

查询:

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 [HY000] ERROR:  Bad timestamp external representation ''

正如我所看到的，有时 SLAEXPIRY 和 SLA_EXPIRY_DATE_TS 列的 NULL 值尝试了以下选项，但运气不佳

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

这里的根本问题是空字符串(即 '')不能转换为时间戳。

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

当然，次要问题是 NULL 不加入。如果您无论如何都想 JOIN NULL，则以下查询应该对您有用。不要指望这种基于表达式的连接有很好的性能。

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
         )
      )