× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I am having a strange problem when attempting to read a file using embedded SQL.

I have a file LEXNEXHITP with 5 date fields among others. I have defined the data structure to read the record into like this:

d record e ds extname(lexnexhitp)

And my select and open statements are like this:

exec sql
declare hits cursor for
select *
from lexnexhitp
where lxrcpdate = :today
ORDER BY LXCOMPANY, LXDCDMATCH, LXPOLICY, lxrcpdate;

exec sql
open hits;
if sqlerrchk('Hits Open') = Error;
return *on;
endif;
return *off;

when I fetch the records using this (the dou loop is to skip the records that have the error):

dou sqlerr <> error;
exec sql
fetch hits into :record;
sqlerr = sqlerrchk('Hits Fetch':replymsg);
if sqlerr = EndOfData;
return *off;
endif;
enddo;

this is the long text for the error:

Message . . . . : Data mapping error on member LEXNEXHITP.
Cause . . . . . : A data mapping error occurred on field LEXNEXHITP_1.LXDOB
in record number 0, record format *FIRST, member number 1, in member
LEXNEXHITP file LEXNEXHITP in library MR0473, 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. 23 -- The data could not be converted from one CCSID
to another. 24 -- The data could not be converted from an ASCII CCSID to an
EBCDIC CCSID, or could not be converted from or to a UCS-2 (Universal Coded
Character Set) CCSID. The addition of shift-out and shift-in characters or
the expansion of single-byte data to a double-byte value caused the
converted length to be larger than the maximum length the result could hold.
25 -- Hash value cannot be computed for the requested query. If the error
occurred on an attempt to read an existing record, file LEXNEXHITP in
library MR0473 identifies the name of the physical file containing the field
contributing to the mapping error. Otherwise, the error occurred on an
attempt to put or update a record in a file and the file name identifies the
open file that contains the field contributing to the mapping error. If the
field name is *N, the field name is not known or is a default value.

This error repeats for 2 other date fields. But only 3 not 5. Seems that the dates that trigger the error are dates before 1945, like 1928. As you can see by the select statement and the data structure record I am attempting to read the file into the data structure that has the same format as the file record.

Is there some setting I need to set to avoid this error? I have tried to user the DATFMT(*ISO) control option but that didn't solve the problem. What else is there?

Marvin
Notice: This e-mail transmission may contain information that is proprietary, privileged and/or confidential and is intended exclusively for the person(s) to whom it is addressed. This message may also contain Protected Health Information (PHI) and must be treated confidentially and handled in accordance with HIPAA and other federal and state privacy laws. Any use, copying, retention or disclosure by any person other than the intended recipient or the intended recipient's designees is strictly prohibited. If you are not the intended recipient or their designee, please notify the sender immediately and delete this e-mail (and any accompanying attachments).

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.