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

On Oct 1, 3:16*am, 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.
>

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

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

On Wed, 01 Oct 2008 02:16:36 +0100, WalterR <wsrue@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.

Dave Hughes wrote:

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

>
>
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