Re: sql select without editing numbers

[Jim Franz <franz9000@xxxxxxxxx>]

Stephen,
The strange thing about varchar_format (in STRSQL) is the trim...

This stmt using digits(var) works  SELECT BID as cust, BTCODE as tran_code,
digits(bwo#) as order,
BREF,date(timestamp_format(char(bdat8),'YYYYMMDD' )) as Tran_Date  ...

CUST    TRAN_CODE  ORDER    Reference                       TRAN_DATE
ESW01       25     2522290  22-0007                         01/03/22
ESW01       27     2522290  22-0007 1421 Old Cypress Trail  04/20/22
ESW01       25     2525848  22-0451 AANOS PROPERTIES LLC    06/28/22

while this stmt does result in ORDER appearing ok, but next column appears
in column 275
SELECT BID as cust, BTCODE as tran_code,
trim(varchar_format(bwo#,'9999999')) as order,
BREF,date(timestamp_format(char(bdat8),'YYYYMMDD' )) as
Tran_Date ...

....+....1....+....2....+....3....+....4
CUST    TRAN_CODE  ORDER
ESW01       25     2450245
ESW01       25     2453785

v7r5 w latest ptfs
I'm ok with the digits solution

Jim

On Thu, Feb 13, 2025 at 10:22 AM Stephen Landess <steve_landess@xxxxxxxxxxx>
wrote:

> SELECT
> VARCHAR_FORMAT(1234.56,'999999999D999')  AS NUMBER
>  FROM SYSIBM.SYSDUMMY1
>
>
> Regards,
> Steve Landess
> (512) 289-0387
> ________________________________
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Jim
> Franz <franz9000@xxxxxxxxx>
> Sent: Wednesday, February 12, 2025 14:46
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
> Subject: sql select without editing numbers
>
> I'm sure its a simple thing, but i need the Order column numbers to not
> have commas (user requests like  2450245). DDS defined tables, packed 7,0
> Google full of hacks that db2 doesn't do.
>
> CUST    TRAN_CODE                ORDER   Ref
> ESW01       25               2,450,245   Widgets
> ESW01       25               2,453,785   Bolts
>
> when i use varchar_format it pads spaces to the right of column and trim
> seems to be ignored
> tia
> Jim Franz
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit:
> https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C02%7C%7C23203b452f1f48e1faf108dd4ba7ca7d%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638749906213661768%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=J7vQrD8Wwj2cC7akdJTUDyUoR7HbL6cvC5rQVfewSnw%3D&reserved=0
> <https://lists.midrange.com/mailman/listinfo/midrange-l>
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at
> https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=05%7C02%7C%7C23203b452f1f48e1faf108dd4ba7ca7d%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638749906213681716%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=MULn2Bdp3SZOfc07eW25AkkLgNBGaHRq1%2FrGLjLoB90%3D&reserved=0
> <https://archive.midrange.com/midrange-l>.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.