I hit my head against this all yesterday, but have not found a clear explanation or way around this error.
I'm trying to run a SQL search over a Physical File that has been externally defined with a DDS file.
The field that is failing is actually representing a date and is supposed to exist as CYYMMDD, but the DDS has defined this field as a NUMERIC(7,0). I say "supposed to" because if I look over the file myself (using DBU), I can see accounts, particularly early ones, have incomplete or completely missing values. I don't know if this is what is causing a problem or not.
This initial query works cleanly when run from STRSQL or through SQuirrel SQL Client (v3.3.0):
However, this really only gives me part of the data set I really need. The subsequent query is what fails:
SELECT AMKEY, AMFDT, AMLDT, AMCUS FROM LIB/FILE1 WHERE AMCUS = 'C' AND AMFDT > 2110000
Error when run from SQuirrel SQL Client v3.3.0 (syntax altered accordingly)
SELECT AMKEY as "Account", AMFDT as "Frst Ord Date", AMLDT as "Last Ord Date",
OMORD# as "OrdNumOpen", OMFRST as "First", OMPRDT as "Purch Date"
FROM LIB/FILE1 inner join LIB/FILE2 on AMKEY = OMACCT and AMFDT = OMPRDT
WHERE AMCUS = 'C' AND AMFDT > 2110000 and OMFRST = 'Y'
Error when run in STRSQL
Error: [SQL0802] Data conversion or data mapping error.
Cause . . . . . : Error type 6 has occurred. Error types and their meanings are:
... 6 -- Numeric data that is not valid. ...
Recovery . . . : The error was caused by data that was not valid or that was too large.
Look at the previously listed messages in the job log (DSPJOBLOG command) or press F10
(Display messages in job log) on this display to determine what row and columns were
involved in the error. Correct the data and then try the request again.
I've been running Google searches, but I'm just not clear what is causing this error. All I know is it's related to the AMFDT field.
Selection error involving AMFDT.
CPD4019: Select or omit error on field AMFDT member FILE1.
Message . . . . : Select or omit error on field AMFDT member FILE1.
Cause . . . . . : A select or omit error occurred in record 0, record format FILE1RA,
member number 1 of file FILE1 in library LIB, because of condition 6
of the following conditions:
... 6 - A data mapping error occurred on the field before the
select or omit operation was attempted. ...
Can SQL just not handle if a numeric field is completely empty? Or is something more subtle going on here? (or something blatantly obvious that I'm apparently oblivious about..)
Absolutely any insight or guidance for additional investigation would be hugely appreciated!