Rob,
A couple months ago I dismissed this function, because the results were difficult to process, but I couldn't remember why. I recreated a consumer of this procedure in RPG and I remember now, but, I may not dismiss it this time. The display_journal_entry_info function returns one row in one result set with multiple columns (1 for each file entry column). Every entry is converted to varchar, so, even the numerics are formatted as character values. That makes sense, because Navigator is only returning a representation of that value, not the real data typed columns. That was why I dismissed it...because I wanted the real data typed columns. Now I'm thinking, however, that I could extract the data type from the SYSCOLUMNS view, and then ask SQL to convert '1235.33' to DECIMAL('1235.33',11,2). I'm not sure what will happen, especially with things like dates and timestamp stuff, but, your suggestion may have me down a road.
My current hope/plan is to create a function that gathers all the results, but then returns the column entries one value at a time either sequentially or by sequence # requests, with the dynamic journal view being written to refer to this function multiple times to get values for each column, and within the view, converting the values to their intended data types from the varchar values.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob Berendt
Sent: Wednesday, May 1, 2019 1:44 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: DISPLAY_JOURNAL and ENTRY_DATA parsing using pure SQL
I think what you are looking for is a function, or something besides a stored procedure, which will return the information that the following does. Something you can join directly with DISPLAY_JOURNAL
call qsys2.display_journal_entry_info
(2, --not sure what 2 means
'journal_library',
'journal name',
Sequence_number,
'receiver library',
'receiver name',
'journal code (aka PT, DL, etc)';
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Darren Strong
Sent: Wednesday, May 1, 2019 1:34 PM
To: midrange-l General Questions (midrange-l@xxxxxxxxxxxxxxxxxx) <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: DISPLAY_JOURNAL and ENTRY_DATA parsing using pure SQL
I developed a hybrid RPG and SQL program to query the DISPLAY_JOURNAL SQL UDTF from IBM, which if you use it, you know that the ENTRY_DATA column comes in as an incomprehensible BLOB. I've used RPG to overlay this BLOB onto a data structure and written this out to a file with the journalled file structure pre-built to be able to view this data. What I'm wondering is, if there is an SQL way to dump this data onto a file and/or structure so that I might be able to build this process as a view, rather than an outfile to further reduce unnecessary file I/O. If it were a view, then I imagine that the program would just create the view, in say QTEMP, and then I could query the fields as desired before the data was actually written anywhere.
The column I'm trying to parse is currently coded as follows:
cast(entry_data as char(5000) for bit data)
--
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@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
--
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@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
