Archives of the TeradataForum
Message Posted: Fri, 24 May 2002 @ 15:29:55 GMT
| Subj: || || Re: Format statement and floating point numbers |
| From: || || Geoffrey Rommel |
| ||Unfortunately, I neglected to state the numbers being output can be anywhere from 16 to 20 non-zero digits in length. Any number
less than 20 digits needs to be zero filled from the left in this 20 byte output field....|| |
How is this possible? The largest number that Teradata can store is DECIMAL(18), which is actually 63 bits of precision. In order
to get 20 decimal digits, you would have to have 67 bits of precision.
I suspect that your sth_employee_id is actually a character field. If so, and if every ID is left-justified and followed by blanks, the
following expression will work:
select substr('00000000000000000000', 1, 21-position(' ' in
substr(sth_employee_id, 1, position(' ' in sth_employee_id)-1)
It were devoutly to be wished that Teradata had a more robust set of character functions. Something like rindex or REXX's "right"
function would be useful in this situation.