I think you need to set the SQL date format like
Exec sql Set Option DatFmt = *ISO; // or could be *USA if you want but I would match the file.
I think you could also specify the data format for the program in the h spec.
Scott
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Cyndi Bradberry
Sent: Tuesday, March 03, 2015 3:01 PM
To: 'Midrange Systems Technical Discussion'
Subject: Date not valid, V7R1 Dynamic SQL
I have a field in a file defined as a date data type, *ISO. I display the date on the screen in *USA format. The problem comes when trying to retrieve the record via a subfile built thru SQL. I get no record due to the date being invalid because the date is 2099-12-01. According to the RPG & DDS books, date in either USA or ISO formats can go to 9999 in the year. If I change the date to 2039, the record is retrieved perfectly.
Additional Message Information
Message ID . . . . . . : CPF5035
Date sent . . . . . . : 03/03/15 Time sent . . . . . . : 14:37:14
Message . . . . : Data mapping error on member SMKCOMIT.
Cause . . . . . : A data mapping error occurred on field SMKCOMIT_1.CEXPDT
in record number 0, record format *FIRST, member number 1, in member
SMKCOMIT file SMKCOMIT in library HOLLIB, because of error code 18. The
error code meanings follow:
1 -- There is data in a decimal field that is not valid.
2 -- A significant digit was truncated.
3 -- A floating point value exceeded the maximum representable value.
4 -- A floating point value was less than the minimum representable value.
5 -- A binary floating point value could not be used for the attempted
operation because it is not a number, or is not valid.
6 -- A floating point value could not be mapped to packed decimal, zoned
decimal, or binary.
8 -- A binary floating point value that is not a number is not allowed as
a key value.
9 -- Division by zero.
10 -- A partial binary key field could not be mapped. The length of the
key did not include the entire binary field.
11 -- A partial key field could not be mapped. The length of the key did
not include the entire binary, floating point, date, or timestamp field.
12 -- There is data in a DBCS field that is not valid.
13 -- Group by results could not be determined because no records were
selected.
14 -- The length of a variable length field is not valid or the data in a
substring variable is not valid.
15 -- A date value is greater than the maximum allowed value.
16 -- A date value is less than the minimum allowed value.
17 -- The format of the data in a date, time, or timestamp field is not
valid.
18 -- There is data in a date, time, or timestamp field that is not valid.
19 -- An unexpected null field was found.
20 -- A field that is not null capable could not be set to null.
21 -- The data has been converted from one CCSID to another. However,
substitution characters were used for characters which could not be
converted.
22 -- A shift-out character was found while converting to a single or
mixed byte CCSID or a substitution character was found. The data could not
be converted.
As defined in the display file:
A #2EXPDT L O 9115DATFMT(*USA)
As defined in the physical file:
A CEXPDT L TEXT('Expiration Date')
A DATFMT(*ISO)
What am I missing ? RTM links appreciated.
TIA,
Cyndi B.
Boise, ID
--
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.
midrange.com
