SQL CAST String to Decimal

Question

SQL0802 Data conversion or data mapping error

The fields not being CAST are decimals. The fields I am trying to CAST are strings.

I have tried different variations or CAST and CONVERT on this case expression. I'm fairly certain this syntax is correct. I am still getting the error though.

CASE WHEN cpssn=amssn THEN amfnam||amlnam
     WHEN cpssn=CAST(maassn as DECIMAL(9)) THEN maafnm||maalnm
     WHEN cpssn=CAST(mpssno as DECIMAL(9)) THEN mppfnm||mpplnm 
END as Name

Could you show what types of fields are `maassn` and `mpssno`? That would enable people to check whether it is even possible to cast to `DECIMAL`. The best would be an excerpt of the data contained on those columns. — Sergio Feo, Dec 15 '15 at 15:06
ForguesR, "SQL0802 Data conversion or data mapping error." is my error message. — madmike, Dec 15 '15 at 15:07
No change, I've triple check the data types coming from my database. "maassn" and "mpssno" are both strings. "cpssn" and "amssn" are decimals. — madmike, Dec 15 '15 at 15:09
Possible duplicate of [Why am I getting a "\[SQL0802\] Data conversion of data mapping error" exception?](http://stackoverflow.com/questions/13075585/why-am-i-getting-a-sql0802-data-conversion-of-data-mapping-error-exception) — itsols, Dec 15 '15 at 15:09
You have non-numeric values in your columns you're trying to CAST. — jarlh, Dec 15 '15 at 15:09
This is hard to answer without knowing the values of `maassn`and `mpssno`. — ForguesR, Dec 15 '15 at 15:10
Why do you store numeric data in a character column? Consider re-designing your tables when you run into problems like this. — jarlh, Dec 15 '15 at 15:10
jarlh, I realize this, but the design of the database is out of my control. — madmike, Dec 15 '15 at 15:12
ForguesR, the values of the fields are sensitive data, but the length of all fields is 9, strings obviously have no precision, decimals have precision of 9. — madmike, Dec 15 '15 at 15:14
Obviously you have a string value which can't be `CAST` to `DECIMAL(9)`. — ForguesR, Dec 15 '15 at 15:14

score 0 · Answer 1 · answered Dec 15 '15 at 15:18

One brute force method uses translate():

(CASE WHEN cpssn = amssn THEN amfnam||amlnam
      WHEN length(translate(massn, 'a0123456789', 'a')) > 0 THEN NULL
      WHEN length(translate(mpssno, 'a0123456789', 'a')) > 0 THEN NULL
      WHEN cpssn = CAST(maassn as DECIMAL(9)) THEN maafnm||maalnm
      WHEN cpssn = CAST(mpssno as DECIMAL(9)) THEN mppfnm||mpplnm 
 END) as Name

Note: I am not intimately familiar with translate() in DB2. The above uses an Oracle convention for removing characters, because the third argument cannot be '' in Oracle. It should still work in DB2.

This should work by first guaranteeing that the only characters in the strings are digits. case is processed in sequential order, so the digit checks should be done before the conversion.

SQL CAST String to Decimal

1 Answers1