timestamp_format( joDate || digits(joTime ), ‘MMDDYYHH24MISS’),
I'd also convert JODATE with digits. Without explicit conversion it is internally converted into VarChar which is left adjusted and suppresses leading zeros. Since your year format is MMDDYY, the month might be a single digit month.
Also replace || with concat, since the || is not international, i.d. may cause problems with different CCSIDs
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan
Sent: Donnerstag, 26. Januar 2017 21:51
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: SQL's TIMESTAMP_FORMAT causes MCH3601
This problem is different from the one I posted here a few days ago (Data mapping error on timestamp in SQL insert). Arco correctly diagnosed that issue with the CHAR function dropping the leading zeros; I fixed the problem by replacing CHAR with DIGITS. More background: The file being queried in the query is the outfile from Carsten Flensburg's EXTJRNDTA utility; that outfile is basically the outfile from DSPJRN with the space consumed by JOESD redefined for the journaled file's record format. So, the joDate and joTime fields used in the timestamp_format come from DSPJRN.
I am now sporadically getting MCH3601 (Pointer not set for location referenced), followed by CPF503E (User-defined function error on member
EJD$CHARGE):
CAUSE: An error occurred while invoking user-defined function TIMESTAMP_FORMAT in library QSYS2. The error occurred while invoking the associated external program or service program QQQSVUSR in library QSYS, program entry point or external name QQQTimestamp_Format, specific name *N.
The error occurred on member EJD$CHARGE file EJD$CHARGE in library DBALEIQX. The error code is 2. The error codes and their meanings follow:
2 -- The external program failed before it completed.
RECOVERY: For error codes 1 and 2, determine the cause of the error from either the SQLSTATE or a previously listed message.
Yesterday, when the query (below) ran from a RUNSQL command in batch, it issued MCH3601 and CPF503E. When I ran the exact same query in interactive SQL, it ran fine. Today, as I was preparing this post, I ran the same query in interactive SQL, and got MCH3601 and CPF503E. When I ran it a second time, it ran fine, and inserted all of the expected records in the target file. Given that the MCH3601/CPF503E seems to be associated with the timestamp_format function, would a call to IBM be in order?
Insert into IQX003H
select case
when (JOCODE, JOENTT) = (‘R’, ‘PT’ ) then ‘I’
when (JOCODE, JOENTT) = (‘R’, ‘UB’ ) then ‘B’
when (JOCODE, JOENTT) = (‘R’, ‘UP’ ) then ‘A’
when (JOCODE, JOENTT) = (‘R’, ‘DL’ ) then ‘D’
end,
timestamp_format( joDate || digits(joTime ), ‘MMDDYYHH24MISS’),
next value for IQX003Hseq,
SU_CRGID, SU_PATID, SU_ACCT, SU_OWNID, SU_DR, SU_RDR, SU_PVDID, SU_ORGID, SU_SUBORG, SU_AGREEID, SU_ID1, SU_ID2, SU_ID3, SU_ORGINID, SU_DOS, SU_CHRG, SU_PAID, SU_BAL, SU_DIAG, SU_CPT, SU_DESC, SU_DWNPMT, SU_WHOPVD, SU_LOCNAM, SU_BALSRC, SU_BALTYP, SU_INSID1, SU_INSUPY1, SU_IREJDE1, SU_INSID2, SU_INSUPY2, SU_IREJDE2, SU_INSID3, SU_INSUPY3, SU_IREJDE3, SU_STATUS, SU_PIFDAT, SU_PIFTIM, SU_PIFUSR, SU_PIFPGM, SU_DELDAT, SU_DELTIM, SU_DELUSR, SU_DELPGM, SU_PVDPTD, SU_LSTPDP, SU_LSTSTMD, SU_LSTSTM#, SU_STMCNT, SU_CODE1, SU_C1RDTE, SU_CODE1DT, SU_CODE2, SU_C2RDTE, SU_CODE2DT, SU_CODE3, SU_C3RDTE, SU_CODE3DT, SU_CODE4, SU_C4RDTE, SU_CODE4DT, SU_CODE5, SU_C5RDTE, SU_CODE5DT, SU_HDATE, SU_FEEPLAN, SU_RREQUST, SU_RREQDTE, SU_RREQWHO, SU_RREQPDT, SU_QCOUNT, SU_QDATE, SU_QWHO, SU_N1, SU_N2, SU_N3, SU_N4, SU_N5, SU_N6, SU_N7, SU_N8, SU_N9, SU_N10, SU_A1, SU_A2, SU_A3, SU_A4, SU_A5, SU_A6, SU_A7, SU_A8, SU_A9, SU_A10, SU_D1, SU_D2, SU_D3, SU_D4, SU_D5, SU_D6, SU_D7, SU_D8, SU_D9, SU_D10, SU_ENTDAT, SU_ENTTIM, SU_ENTUSR, SU_ENTPGM, SU_UPDDAT, SU_UPDTIM, SU_UPDUSR, SU_UPDPGM from QTEMP/EJD$CHARGE
- Dan
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://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:
http://amzn.to/2dEadiD
midrange.com
