casting and padding problem

[Thanos]

hello,

update trl_info
set rec_count = repeat('0', 11- Length(cast((select count(*) from
tableA) as char(5))))
concat cast((select count(*) from tableA) as char(5))

The problem is the that value of count(*) may be big or smaller than 5
characters long so how to dynamically change the casting of the
interger to a char that is the same length of the count(*) size so
that the right of amount of leading '0's will be added for a total
character length of 11.

Thanks

WofD

[Philip Sherman]

I have no idea how to dynamically change the casting but I got the
result (UDB 8.1 FP7) you want with:

substr(right('00000000000'||digits(count(*)),11),1 ,11)

1. "count(*)" - Count the number of rows
2. "digits" - Convert to a string with leading zeros
3. "||" - Concatenate 11 zeros onto the front of the result
4. "right" - Use only the rightmost 11 characters
(The following is needed for command line usage to truncate the
generated string. You may need it too.)
5. "substr" - Use only the first 11 characters of the string

This also has the advantage of performing the count only once. I leave
it to you to code this as a "case" statement to insert the correct
number of leading zeros.

Phil Sherman
Thanos wrote:

hello,

update trl_info
set rec_count = repeat('0', 11- Length(cast((select count(*) from
tableA) as char(5))))
concat cast((select count(*) from tableA) as char(5))

The problem is the that value of count(*) may be big or smaller than 5
characters long so how to dynamically change the casting of the
interger to a char that is the same length of the count(*) size so
that the right of amount of leading '0's will be added for a total
character length of 11.

Thanks

WofD

[Thanos]

would this do the trick??

update trl_info
set rec_count = repeat('0', 11- Length(ltrim((cast((select count(*) from
tableA) as char(5))))))
concat ltrim(cast((select count(*) from tableA) as char(11)))

in******@hotmail.com (Thanos) wrote in message news:<ea**************************@posting.google. com>...

hello,

update trl_info
set rec_count = repeat('0', 11- Length(cast((select count(*) from
tableA) as char(5))))
concat cast((select count(*) from tableA) as char(5))

The problem is the that value of count(*) may be big or smaller than 5
characters long so how to dynamically change the casting of the
interger to a char that is the same length of the count(*) size so
that the right of amount of leading '0's will be added for a total
character length of 11.

Thanks

WofD

[Tokunaga T.]

How about this?
update trl_info
set rec_count = DIGITS(CAST((select count(*) from tableA) AS INTEGER))