Re: Access QAUDJRN Packed Numeric Data using Query/400 (QU1)

On 15/04/2010, at 8:29 AM, Graap, Kenneth wrote:

I'm trying to do something using Query/400 (QU1) that I thought would take a couple of seconds and it has turned into several hours with no luck!

The QAUDJRN ZC data puts out a string of data that contains Character and Packed Numeric's

Column 1 - 31 is all CHARACTER DATA ... Column 32-34 is a Packed Numeric (Dec 5 0)

Columns 1 to 30 are character ... columns 30 to 32 look like a packed number

Code . . . . . . . . : T - Audit trail entry
Type . . . . . . . . : ZC - Object change access

Entry specific data
Column *...+....1....+....2....+....3....+....4....+....5
00001 'CPOWER520 QSYS *LIB " '

Same Data Displayed in HEX ...

Code . . . . . . . . : T - Audit trail entry
Type . . . . . . . . : ZC - Object change access

Entry specific data
Column * . . . + . . . . 1 . . . . + . . . . 2 . . . . +
00001 'C3D7D6E6C5D9F5F2F04040D8E2E8E24040404040405CD3C9C2'
00026 '4040404000007F404040404040404040404040404040404040'

You can see that the value I'm after in this example is 00007

I'm trying to create a Query Report showing the data in Column 32-34 ...

Given the data you show that would be columns 30 to 32 inclusive.

But I can't figure out how to change this Packed Numeric data into a Character that can be displayed on my report.

You can't. Query has no support to convert a character field that just happens to contain a value that looks like a packed number into a numeric field.

The data I'm trying to process is a substring of field AUDATA ... (AUDATA, 32, 3) to be exact... I realize the AUDATA field is defined as CHAR with a length of 1000 ... This field contains the character data and this bit of packed Data .... I imagine this is the root of my problem.

The DB file I'm running my queries over is defined like this:

<extraneous field definitions deleted>

Coded Character Set Identifier . . . . . : 37
AUDATA CHAR 1000 1000 111 Both Entry data
Field text . . . . . . . . . . . . . . . : Entry data
Coded Character Set Identifier . . . . . : 37

Anyone have a tip that can help me out?

Stop trying to do this with Query. As far as I know none of the various query tools have a way to convert a packed character value to a number. SQL has support to convert character strings containing digits into various numeric data types but they all expect strings like '12345' (i.e. X'f1f2f3f4f5' rather than your x'00007f' example).

The only way I know to convert that sort of value is via a program where you re-map the "character" storage as "numeric" via a data structure.

Thus create a PF with the fields and data types you want. Create a trivial program in some HLL and read the journal records, stick the problem field in a data structure, and write the new output records. Then run your query across the new output file (or just use the HLL to generate the required report directly).

I have already tried this - TYPE SUBSTR(AUDATA, 32, 3)
CHARTYPE DIGITS(TYPE)

... that didn't work ...

Not surprising:
SUBSTR returns a character field from a character field
DIGITS returns a character field from a numeric field

Regards,
Simon Coulter.
--------------------------------------------------------------------
FlyByNight Software OS/400, i5/OS Technical Specialists

http://www.flybynight.com.au/
Phone: +61 2 6657 8251 Mobile: +61 0411 091 400 /"\
Fax: +61 2 6657 8251 \ /
X
ASCII Ribbon campaign against HTML E-Mail / \
--------------------------------------------------------------------