Leading Zero's on Numeric Fields

[JezT]

I am wanting ao add leading zero's to a numeric field if it is not 10 characters long.

I know the FMT Function is used for string fields but what can I use for a numeric field ?

[xcb]

You can still use FMT, try FMT([your number], "10'0'L") which will append up to 10 leading zeros.

[pramod_au]

I am wanting ao add leading zero's to a numeric field if it is not 10 characters long.

I know the FMT Function is used for string fields but what can I use for a numeric field ?

Alternate Way:

If LEN(NUMERIC_FIELD) < 10 then oconv(NUMERIC_FIELD,"MR%10") else
NUMERIC_FIELD

Hope this helps.

thanks
Pramod

[ray.wurlod]

There are no data types within DataStage; everything is handled as string unless the context requires otherwise. So you can use FMT on "numeric" fields. You can use NUM to test whether a string can legally be regarded as numeric.

Cameron's reply can be improved in two ways. Because you specified numeric, he should have used right justified rather than left justified.

Code: Select all

Fmt(YourNumber, "10'0'R")

Cameron's solution ("10'0'L") would give you trailing zeroes!

An alternative (short cut?) syntax is to use the special mask character "%" which specifies a background character of "0", to achieve the same result.

Code: Select all

Fmt(YourNumber, "R%10")

which is shorthand notation for

Code: Select all

Fmt(YourNumber, "R%%%%%%%%%%")

both of which mean "lay down YourNumber right-justified over a background of ten zeroes".