RE: Viewing packed decimal field as a string

Let me step back a bit...
Let's say someone had a packed number. Just for grins we'll say it was
12345. And you want to see all journal entries which contain 12345, but
the packed representation? Basically someone keyed in a bogus number and
you want to see all the tables which recently got updated with this entry
so you can audit them.

Let's create a table.

CREATE TABLE ROB/MATTOLSON (MYPACKED DEC (7 , 0), MYCHAR CHAR (5 ))

DSPFD ROB/MATTOLSON
File is currently journaled . . . . . . . . : Yes
Current or last journal . . . . . . . . . . : USERS
Library . . . . . . . . . . . . . . . . . : #MXJRN

INSERT INTO ROB/MATTOLSON (MYPACKED, MYCHAR) VALUES(12345, 'HI')

DSPPFM ROB/MATTOLSON
F10=Display hexadecimal (toggle)

Now let me try this:
SELECT journal_code, journal_entry_type, object, object_type, X.*
FROM TABLE (QSYS2.Display_Journal(
JOURNAL_LIBRARY => '#MXJRN',
JOURNAL_NAME => 'USERS',
STARTING_TIMESTAMP => TIMESTAMP('2018-09-11-15.40.01'),
STARTING_RECEIVER_NAME => '*CURCHAIN'
) ) AS X
WHERE JOB_USER IN('ROB')
;
My entry_data is this
0012345FC8C9404040
Pretty visible, eh?
However I cannot do a LIKE comparison against that because it is an
incompatible data type.
So let me try this
SELECT journal_code, journal_entry_type, object, object_type,
cast(entry_data as char(32000)), X.*
...
Ok, now I see special characters.
Let me try this
SELECT journal_code, journal_entry_type, object, object_type,
hex(substr(cast(entry_data as char(32000)), 1, 4)), X.*
I see 0012345F in that column

...
as much fun as this is I've got to get back to "real work". But I think I
understand what you are trying to do.

Basically the problem is that you cannot use a like because it's
incompatible data types. And when you cast it to character it's no longer
as readable.

Right?


Rob Berendt