Hello,
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:
Error when run from SQuirrel SQL Client v3.3.0 (syntax altered accordingly)
Error when run in STRSQL
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.
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!
Thank you!
Liz
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):
Code:
SELECT AMKEY, AMFDT, AMLDT, AMCUS FROM LIB/FILE1 WHERE AMCUS = 'C' AND AMFDT > 2110000
Code:
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'
Code:
[COLOR="#FF0000"]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. ...[/COLOR] 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. SQLState: 22023 ErrorCode: -802
Code:
[COLOR="#FF0000"]Selection error involving AMFDT.[/COLOR] [B]JobLog Continues:[/B] [COLOR="#FF0000"]CPD4019: Select or omit error on field AMFDT member FILE1.[/COLOR] 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!
Thank you!
Liz
Comment