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

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)

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

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

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:

Field Level Information

Data Field Buffer Buffer Field
Field Type Length Length Position Usage
<<SNIP>>
AUDATA CHAR 1000 1000 111 Both
Field text . . . . . . . . . . . . . . . : Entry data
Coded Character Set Identifier . . . . . : 37

Anyone have a tip that can help me out?

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

... that didn't work ...

The data of interest actually appears to be from position 30 for three bytes.

The /result field/ expression TYPE=SUBSTR(AUDATA,30,3) will result in the string of data x'00007F'. AFaIK a character string is not valid for the operand of the DIGITS scalar function; i.e. the result field CHARTYPE should not be allowed as shown.?

Given the data is always both positive and integer, the following expressions would suffice to present the numeric result as a character string; albeit easily combined into one expression:

TYPE = SUBSTR( AUDATA, 30, 3)
CHARTYPE = SUBSTR( HEX(TYPE), 1, 5)

More generically, the AUDATA column contains /program described/ data. Interestingly, the column is incorrectly defined with a non-hex CCSID; i.e. an apparent defect in the column definition. Very possibly the assumptions of always both positive and integer may be invalid. The Query/400 requires that the file itself must know and expose the description of its data via column\field definitions; these can come from externally described files, or program described files which are linked to an IDDU definition. The given scenario utilizes an externally described file, but for which some of its data is nor externally described. Program described column data is effectively unusable in the Query/400 feature, as there is no capability in the Query/400 [nor SQL for that matter] to represent some number of bytes of a character string column, as anything other than what can be CAST from a character string. There is no CASTing from an internal representation of a number, from a character string which contains the BCD [Binary Coded Decimal representation], to a numeric data type. A /direct map/ or overlay feature would be required, whereby the function indicates that a given number of bytes of string data represents a specified data type directly; the %BIN feature of CL is such a function, but there is nothing similar in Query/400 or the SQL.

However a CREATE VIEW could redefine a substring of the column as a new column, and that new VIEW *FILE could be queried instead. The VIEW would need to includes a UDF [User Defined Function] scalar which accepts the character string data as input, and returns a result which is one of the numeric data types. Since the RPG allows defining a variable over a string of data, an overlay, that could be used in an External UDF. Although a SQL UDF can also effect the conversion, using the HEX() of the SUBSTRING() data, it is not really the prettiest choice; i.e. easier to use a feature which allows a /direct map/ of the binary data to the desired data type.

Regards, Chuck