0

is there any way to pad 0's to smallint in DB2 for mainframe z/OS. I am not writing any program but running query directly on DB2 QMF.

Field definition:

Column Name  Col type length
some_column  Smallint  2

 some_column     result
 -----------     ------
  288         ==>  0288
  88          ==>  0088
  1224        ==>  1224
Tokci
  • 1,220
  • 1
  • 23
  • 31
  • This is general idea on most systems: `right('000'+cast(c as varchar(10)), 4)` – shawnt00 Mar 13 '15 at 02:41
  • @shawnt00, will that really work with arbitrary numbers? (Sometimes 1 digit, sometimes 2 etc...) – jarlh Mar 13 '15 at 08:21
  • I get following error: i am running this query directly on DB2 and not using any program. I replaced c with column name DSNT408I SQLCODE = -171, ERROR: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 1 OF + IS INVALID – Tokci Mar 13 '15 at 15:04

1 Answers1

0

In all currently supported versions of DB2 for z/OS there's a function DIGITS() that does exactly what you need.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
  • DIGITS() function will not and is not working here because the function returns a 5 length string if argument is small integer . The argument in my case is small int. I am getting result as 00360 00288 and I want 0360 0288 – Tokci Mar 13 '15 at 14:46
  • @AshishSriAshish So take only the right-most 4-character substring. – user2338816 Mar 14 '15 at 03:20