Running on a mainframe. ZOS, DB2 V7.2.

database field is defined as
PSTL_CDE DECIMAL(9) NOT NULL

I am trying to switch it to a CHAR field to preserve leading zeros. (actually it's a bit more complicated than that, because it's used in a View with another table that's defined as a char.)

But here is my question. I have several different ways to select and convert the field. Ideally, I want a 9 character field, no decimal point. Just like if it was defined as CHAR(09).

Here's a few of my results
ORIG DEC9 = "140631834."

CHAR(PSTL_CDE) = "140631834." => I don't want the decimal point

SUBSTR(CHAR(ADDR_PSTL_CDE),1,09) = "14063183" => Missing the '4'.

SUBSTR(CHAR(ADDR_PSTL_CDE),1,10) = "140631834" => the number I want, but now it's CHAR(10). Not a huge deal, but why do I have to use 10?

Why do I have to make it a CHAR(10)? The DB2 manual (pg 283, dsnsqh12) says the length parm should be Length - Start + 1 (9-1+1) or 9.

Syntax is SUBSTR(string, start, length).

I think it has something to do with the CHAR function. According to the SQL reference, for "Decimal to Character" "the lenght of the result is 2+p, where p is the precision of the decimal-expression. (In your case 2+9 = 11)