db2 - db2 中的“DECFLOAT”错误

Question

Lookup Error - DB2 Database Error: ERROR [22018] [IBM][DB2/AIX64] SQL0420N Invalid character found in a character string argument of the function "DECFLOAT".

询问 - - -

SELECT 
   MSISDN,
   CONTRNO,
   TRANSDATE,
   TARIFF_GROUP,
   ACT_DURATION,
   BILLTEXT,
   GROSS_AMOUNT,
   CASE
       WHEN TARIFF_GROUP IN('PAG2')
       THEN DECIMAL((DECIMAL(ACT_DURATION,10,4)/10),20,4)*0.01
       ELSE 'CHECK'
   END RA_RATE
FROM HISTCALLS
WHERE call_type IN (50,
                    54)
  AND TRANSDATE = CURRENT date - 1 DAY

Answer 1

问题出在您的 case 表达式中。
对于某些行，单个结果列不能是数字，而在其他行中不能是字符。

SELECT 
    MSISDN,
    CONTRNO,
    TRANSDATE,
    TARIFF_GROUP,
    ACT_DURATION,
    BILLTEXT,
    GROSS_AMOUNT,
    CASE
        WHEN TARIFF_GROUP = 'PAG2'
        THEN DECIMAL(ACT_DURATION * 0.001, 10,4)
        ELSE null
    END RA_RATE
  FROM HISTCALLS
  WHERE call_type IN (50, 54)
    AND TRANSDATE = CURRENT date - 1 DAY