Under the "interpret" SQL documentation, I see that a date should be represented by a 4 byte expression, but, when you create a table with a date column, it looks to me like it takes 10 bytes, so, it seems that interpret was not designed the same as the date storage method. I suspect I'm also missing something, but, it does lend some explanation to the results you're seeing.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles Wilt
Sent: Monday, May 9, 2022 12:53 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: DISPLAY_JOURNAL, INTERPRET a DATE column
CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
on a v7.4 box that's reasonably current on PTFs...
Using the QSYS2.DISPLAY_JOURNAL() UDTF to look at the journal entries for a specific table...
I expected this to work..
, interpret(substr(entry_data, 297, 5)as DECIMAL(9,2)) as CORE_VALUE , interpret(substr(entry_data, 302, 9)as CHAR(9)) as SHIPPER_NUMBER , interpret(substr(entry_data, 311, 4)as DATE) as RECEIVE_DATE , interpret(substr(entry_data, 315, 22)as VARCHAR(20)) as COUNTER_PERSON_NAME
However, I get a cast error... looking at the entry data from pos 311 on, , substr(entry_data, 311) as hmm I see
F0F0F0F160F0F160F0F100014040404040404040404040404040404040404040
So it seems the date data type is surfaced as a CHAR(10) in ENTRY_DATA as this works , interpret(substr(entry_data, 297, 5)as DECIMAL(9,2)) as CORE_VALUE , interpret(substr(entry_data, 302, 9)as CHAR(9)) as SHIPPER_NUMBER , interpret(substr(entry_data, 311, 10)as char(10)) as RECEIVE_DATE , interpret(substr(entry_data, 321, 22)as VARCHAR(20)) as COUNTER_PERSON_NAME
Am I missing something?
Thanks!
Charles
--
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@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
