'DECFLOAT' error in db2

Question

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

Query-----

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

What's the type of `ACT_DURATION`? If it's character, have you checked it for problem values? What is the current culture of the session? Why are you calling `DECIMAL` twice? You're only widening the top end, which shouldn't effect the end result. For that matter, you can combine the multiplication and division, too. — Clockwork-Muse, Jul 01 '15 at 12:07
@Clockwork-Muse Where is DECFLOAT coming from if the query only says DECIMAL (ie. Packed decimal format) — WarrenT, Jul 01 '15 at 12:20
In recent DB2 versions if you attempt arithmetic operations on string operands they are implicitly cast to `DECFLOAT`. I guess the query optimizer removes the redundant explicit casts in this case. This should be evident in the rewritten query text as shown by `db2exfmt`. — mustaccio, Jul 01 '15 at 12:33

score 5 · Accepted Answer · answered Jul 02 '15 at 05:16

5

The problem is in your case expression. A single result column cannot be numeric for some rows and character in others.

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

answered Jul 02 '15 at 05:16

WarrenT

4,502
19
27

Exactly The solution.. thanks a lot.. Its that A Number Column just cannot hold a Numeric and Varchar value at the same time worked fine when I replaced 'Check' with -999... – Jabed Jul 10 '15 at 18:47

'DECFLOAT' error in db2

1 Answers1