Re: sql select without editing numbers -- MIDRANGE-L

One of the huge benefits of DB2 for i is the ability to include edit codes
in DDS source.

Because I define text and column headings in pretty mixed-case, and because
I'm careful with edit codes, users' queries from my DDS database come out
looking nice. And often a user-generated report will do the job, which
means I don't have to write a program. "Embedded" edit codes make designing
display and printer files much easier too.

Yes, you can use functions to reformat column-level data (which I do to
convert ISO dates to MM-DD-YY) but then you lose the column heading. Most
of my users want a spreadsheet (thank you ACS) and I can get results out
quickly with column headings.

On Wed, Feb 12, 2025 at 2:31 PM Jim Franz <franz9000@xxxxxxxxx> wrote:

it worked fine - as soon as i saw Charles suggestion i recognized it
(getting old...)
Users ask for a quick 1 time report and I usually sql it - but they get
picky about editing columns.
thx
jim

On Wed, Feb 12, 2025 at 5:26 PM Rob Berendt <robertowenberendt@xxxxxxxxx>
wrote:

Jim
Just try Charles suggestion. If it leaves leading zeros and you find

that

undesirable, let us know

On Wed, Feb 12, 2025 at 5:24 PM Jim Franz <franz9000@xxxxxxxxx> wrote:

green screen STRSQL
there are some edit controls in the session attributes

Statement processing . . . . . *RUN *RUN, *VLD, *SYN
SELECT output . . . . . . . . 1 1=Display, 2=Printer
3=File
Commitment control . . . . . . *NONE *NONE, *CHG, *CS, *ALL,

*RR

*NC, *UR, *RS
Date format . . . . . . . . . *MDY *JOB, *USA, *ISO, *EUR,
*JIS
*MDY, *DMY, *YMD, *JUL
Date separator . . . . . . . . '/' *JOB, '/', '.', ',',

'-'

' ', *BLANK
Time format . . . . . . . . . *HMS *HMS, *USA, *ISO
*EUR, *JIS
Time separator . . . . . . . . ':' *JOB, ':', '.', ','
' ', *BLANK
Data refresh . . . . . . . . . *ALWAYS *ALWAYS, *FORWARD
Allow copy data . . . . . . . *YES *YES, *OPTIMIZE, *NO
Naming convention . . . . . . *SYS *SYS, *SQL

Jim

On Wed, Feb 12, 2025 at 5:16 PM Rob Berendt <

robertowenberendt@xxxxxxxxx

wrote:

Vern,
It's just best to assume that perhaps you are using a different tool

than

Jim does, or that his numerics are defined slightly different (edit

code

perhaps) or some such thing.
I'd go with Charles suggestion.

On Wed, Feb 12, 2025 at 4:46 PM Vern Hamberg via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Hi Jim

I have to ask what tool you are using to run the SELECT - I don't
usually see commas when the columns are numeric. Here're the

results

for

1 row of QIWS/QCUSTCDT -

CUSNUM LSTNAM INIT STREET CITY STATE ZIPCOD CDTLMT

CHGCOD

BALDUE CDTDUE
938485 Johnson J A 3 Alpine Way Helen GA

30545

9999 2 3987.5 33.5

CUSNUM, ZIPCOD, CDTLMT, and BALDUE are all Numeric

*Regards*

*Vern Hamberg*

<cid:part4.i2taBQ1w.k6cbi0o0@centurylink.net>

On 2/12/2025 3:05 PM, Charles Wilt wrote:

DIGITS(ORDER)

HTH,
Charles

On Wed, Feb 12, 2025 at 1:46 PM Jim Franz<franz9000@xxxxxxxxx>

wrote:

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://lists.midrange.com/mailman/listinfo/midrange-l
or email:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
athttps://archive.midrange.com/midrange-l.

Please contactsupport@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.

--
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.

--
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.

--
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.

--
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.