-1

I need to show 3456789 as 34,56,789 in the output of below select query in db2

SELECT ' RUN TIME: ' CONCAT CURRENT_TIME CONCAT SPACE(39) CONCAT 'PROCESS' CONCAT SPACE(44) CONCAT '3456789' FROM sysibm.sysdummy1 WITH UR;

Current Output is: RUN TIME: 14.13.24 PROCESS 3456689

Robin
  • 87
  • 1
  • 12

1 Answers1

0

'3456789' is already a string...

Assuming your data actually starts out numeric, take a look at varchar_format()

SELECT ' RUN TIME: ' CONCAT CURRENT_TIME 
  CONCAT SPACE(39) CONCAT 'PROCESS' CONCAT SPACE(44) 
  CONCAT varchar_format(3456789,'9,999,999') 
FROM sysibm.sysdummy1 WITH UR;

Note it's usually a bad practice to do this formatting in the DB, it's usually best left to the UI.

Charles
  • 21,637
  • 1
  • 20
  • 44
  • I am getting following error SQL Error [22007]: Format string "9,999,999" is not valid for the "SYSIBM.VARCHAR_FORMAT" function.. SQLCODE=-20447, SQLSTATE=22007, DRIVER=4.21.29 – Robin Apr 27 '21 at 15:16
  • @Robin what platform and version of Db2? Works for me on Db2 for IBM i 7.4. – Charles Apr 27 '21 at 15:19