Leading Zeros

[WalterR]

On a z/OS DB2 v8 platform, this refers to formatting a SMALLINT field as
two zoned-decimal (character) numerics, with a leading zero when there
is only one significant digit. I tried several different CAST formats
with no luck.

Is this possible?

Thanks,

Walter Rue

[Lennart]

On Oct 1, 3:16*am, WalterR <[e-mail]:

On a z/OS DB2 v8 platform, this refers to formatting a SMALLINT field as
two zoned-decimal (character) numerics, with a leading zero when there
is only one significant digit. *I tried several different CAST formats
with no luck.

Don't know if it works on your platform, nor if it fits you needs,
but ...

db2 "with T(n) as (values 11,2,63,4) select case when n < 10 then '0'
else '' end || char(n) from T"

/Lennart

[ChrisC]

Or, you can use RIGHT and DIGITS to do the same:

with o(n) as (values 11,2,63,4),
t(n) as (select cast(n as smallint) from o)
select right(digits(n),2) from t

-Chris

[Dave Hughes]

On Wed, 01 Oct 2008 02:16:36 +0100, WalterR <ws***@verizon.netwrote:

On a z/OS DB2 v8 platform, this refers to formatting a SMALLINT field as
two zoned-decimal (character) numerics, with a leading zero when there
is only one significant digit. I tried several different CAST formats
with no luck.

Is this possible?

Thanks,

Walter Rue

SELECT RIGHT(DIGITS(CAST(1 AS SMALLINT)), 2) AS I
FROM SYSIBM.SYSDUMMY1

UNION ALL

SELECT RIGHT(DIGITS(CAST(4 AS SMALLINT)), 2) AS I
FROM SYSIBM.SYSDUMMY1

UNION ALL

SELECT RIGHT(DIGITS(CAST(16 AS SMALLINT)), 2) AS I
FROM SYSIBM.SYSDUMMY1;

I
-----
01
04
16
Cheers,

Dave.

[WalterR]

Dave Hughes wrote:

On Wed, 01 Oct 2008 02:16:36 +0100, WalterR <ws***@verizon.netwrote:

>On a z/OS DB2 v8 platform, this refers to formatting a SMALLINT field
as two zoned-decimal (character) numerics, with a leading zero when
there is only one significant digit. I tried several different CAST
formats with no luck.

Is this possible?

Thanks,

Walter Rue

SELECT RIGHT(DIGITS(CAST(1 AS SMALLINT)), 2) AS I
FROM SYSIBM.SYSDUMMY1

UNION ALL

SELECT RIGHT(DIGITS(CAST(4 AS SMALLINT)), 2) AS I
FROM SYSIBM.SYSDUMMY1

UNION ALL

SELECT RIGHT(DIGITS(CAST(16 AS SMALLINT)), 2) AS I
FROM SYSIBM.SYSDUMMY1;

I
-----
01
04
16
Cheers,

Dave.

I had been confidently looking for something built-in, but I guess I was
assuming it would have a more obvious name. Anyway, DIGITS is it.
Thanks also for the CASE solution. I tried both.

Walter Rue