Hi All,

Trying to retrieve data from QSYS2.DISPLAY_JOURNAL using below SQL. It works fine, except that GroupNumber, which is a numeric (Packed) field. It does not show the actual value, instead with special characters. May I know how can I convert it into numeric, in this SQL? Thanks!

SELECT
SEQUENCE_NUMBER, journal_code, journal_entry_type,
-- entry_data field split in the original file fields
CAST(cast(substring(entry_data, 1, 1) as char(1) for bit data) as CHAR(1) CCSID 1141) as Active,
CAST(cast(substring(entry_data, 2, 1) as char(1) for bit data) as CHAR(1) CCSID 1141) as DefaultValue,
CAST(cast(substring(entry_data, 3, 15) as char(15) for bit data) as CHAR(15) CCSID 1141) as Index,
CAST(cast(substring(entry_data, 18, 10) as char(10) for bit data) as CHAR(10) CCSID 1141) as Code,
CAST(cast(substring(entry_data, 28, 3) as char(3) for bit data) as CHAR(3) CCSID 1141) as GroupNumber,
CAST(cast(substring(entry_data, 33, 100) as char(100) for bit data) as CHAR(100) CCSID 1141) as Value
FROM TABLE (
                 QSYS2.DISPLAY_JOURNAL( 'JOURNALS', 'PRODJRN', OBJECT_NAME=>'CONTACT',
                 STARTING_RECEIVER_NAME => '*CURAVLCHN',
                 OBJECT_LIBRARY=>'MYLIB',
                 OBJECT_OBJTYPE=>'*FILE',
                 OBJECT_MEMBER=>'CONTACT',
                 JOURNAL_ENTRY_TYPES =>'DL'));

INTERPRET(CAST(cast(substring(entry_data, 28, 3) as char(3) for bit data) as CHAR(3) CCSID 1141)  AS DEC(5,0)) AS "Count"

https://www.rpgpgm.com/2020/10/using-sql-to-extract-packed-decimal.html

Thanks for your suggestion Robert.

INTERPRET(CAST(cast(substring(entry_data, 28, 3) as char(3) for bit data) as CHAR(3) CCSID 1141)  AS DEC(5,0)) AS "Count"

When tried with INTERPRET function, got below error highlighting "CAST(cast(substring(entry_data,"

SQL State: 42601
Vendor Code: -199
Message: [SQL0199] Keyword CAST not expected. Valid tokens: , FROM INTO. Cause . . . . . :   The keyword CAST was not expected here.  A syntax error was detected at keyword CAST.  The partial list of valid tokens is , FROM INTO. This list assumes that the statement is correct up to the unexpected keyword.  The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery  . . . :   Examine the SQL statement in the area of the specified keyword.  A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.
Processing ended because the highlighted statement did not complete successfully

So tried with below one, and output showed correctly, but there was an error message before:

INTERPRET(cast(substring(entry_data, 28, 3) as char(3) for bit data) AS DEC(5,0)) AS "Count"

SQL State: HY000
Vendor Code: -99999
Message: Internal driver error. (Low-order nibble of the byte at array offset 3079 is not valid. Byte value: 40.)

Any thoughts here?

Hi Robert,

Was able to find a solution for packed decimals with below conversion:

dec(substr(hex(substr(entry_data, 28, 3)),1,5),5,0) as GroupNumber,

Thanks everyone!

Let me echo Robert's advice
https://www.ibm.com/docs/en/i/7.5?topic=functions-interpret

Thanks Patrick. As mentioned in the above reply, not having success on INTERPRET yet. May be I am missing something.

INTERPRET is used to properly convert.

Anyway,

to audit what happened to tables/files (i.e. who deleted this records etc.) I cannot recommend more the excellent command line tool (downloadable online and obviously please consider donate to the author if useful).

EXPJRNE

it extract the journal entries recreating in output exactly the correct original fields of the particular journalled file, can extract from a chain, automatically resolve journals to use etc. After one  one can query the extracted file, copy record back and do whole analysis.

Excellent and very useful tool to have on a machine to audit file modifications and will save you a lot of time, useful also in general problem debugging in complex transactions.

A quick alternative is to use the GUI based ACS to view the generic journal entries (and it can show the fields properly on a one by one basis).

Thanks for your suggestion. Will explore this option too.

This is how I have done it in the past

Assuming always positive: dec( substr( hex( substr( entry_data, 28, 3)), 1, 5), 5, 0) as GroupNumber

If could also be negative it gets a bit more messy as you need a case statement checking substr( hex( substr( entry_data, 28, 3)), 5, 1)  if this is 'F' then positive, else negative

Thank you @Colin Grierson