as400 sql – non numeric data in numeric field
is there a function I can use to avoid and abend when sql hits a numeric field that contains non-numeric data. sql: select * from LPDtst/ARCOMB WHERE ACAOF# <0 Error: Selection error involving field ACAOF#.
Software/Hardware used:
Software/Hardware used:
ASKED:
January 14, 2010 11:23 PM
UPDATED:
January 20, 2010 3:23 PM
Answer Wiki:
Just some old experience on the subject:
Non-numeic data may well be contained in a numeric field. In the old days with S36, the op.system did not care whether a numeric field contained a zero or a blank - both worked as a zero. Native OS400 does not have this 'flexibility' except for the 'System/36 environment'.
If your files contain data that has been migrated from System/36 and even converted to externally DDS-described files, then your file may well contain non-numerics in numeric fields.
An easy way to avoid (repair!) the SQL-problem, is to use Query/400 as your repair-tool:
The method copies your file/data to a workfile and you copy this 'repaired' workfile back to the original file. Here's how:
<pre>1. WRKQRY
2. use the "Select output type and output form" and set "Output type" to '3'.
3. fill in a libraryname (fx QRPLOBJ) and a name for the workfile (fx WORKFILE) and set "Data in file" to '1'.
4. use the "Specify processing options" and set "Ignore decimal data errors" to 'Y'
5. end querydefinition and run it.
6. CPYF FROMFILE(QRPLOBJ/WORKFILE) TOFILE(MYLIB/MYFILE) MBROPT*REPLACE) </pre>
To see all answers submitted to the Answer Wiki: View Answer History.
Are you sure that it’s a “data” problem? From the SQL Reference:
Identifiers:
An identifier is a token used to form a name. An identifier in an SQL statement is one of the following types:
Note: $, @, #, and all other variant characters should not be used in identifiers because the code points used to represent them vary depending on the CCSID of the string in which they are contained. If they are used, unpredictable results may occur. For more information about invariant characters, see the Invariant character set topic in the i5/OS Information Center. End Note
It could indeed be a data problem, but it’s worth verifying.
SQL performs data validation when data is written. Native database I/O performs validation when data is read. (Assumption is that data will be read more often than written. Perform validation once.)
Unless you code a SQL procedure, you might have trouble working up useful handling of invalid data.
You might try coding the identifier as WHERE “ACAOF#” <0 — quoting the name might make a difference.
Tom
I have to agree with TomLiotta – by defintion a numeric data type column cannot contain non-numerics. With one possible (and I repeat possible) exception. If the column is NULLABLE and it is marked as NULL (that is, the NULL indicator is set) then the data in the column might (repeat might) possibly (repeat possibly) be nonnumeric. After all, even if it is NULL, there has to be SOMETHING in the column – x’00′ thru x’FF’.
by defintion a numeric data type column cannot contain non-numerics.
This is only true for SQL tables that are populated by SQL.
This doesn’t seem to be a SQL table. It seems to be a native (DDS) file that was probably populated through native database I/O rather than SQL. As such, data validation would not happen at ‘write-time’.
On this platform, SQL is allowed to access native files and native access is allowed to access SQL tables and views. The results can be tricky, though, when it gets down to internal details. They aren’t totally interchangeable.
IOW, the field can indeed contain non-numeric data. Worse, AFAIK, unless the file is accessed in a SQL procedure that includes appropriate error handling (which might not be sufficient), the file will need to be processed with native I/O in order to catch and handle the error at ‘read-time’. SQL isn’t well prepared for ‘read-time’ errors.
In any case, I’d try to ensure that my SQL stayed within the SQL standard first. Then I’d go after data problems.
Is field ACAOF# defined as char or numeric ? If this is a char field that contains non-numeric data, this error will occur becuase the where clause is trying to cast the field to a numeric in order to test.