as400 sql – non numeric data in numeric field

75 pts.
Tags:
AS/400
AS/400 SQL
SQL
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#.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    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:
    • v “SQL identifiers”
    • v “System identifiers”
    • v “Host identifiers” on page 50
    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
    125,585 pointsBadges:
    report
  • Meandyou
    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'.
    5,220 pointsBadges:
    report
  • TomLiotta
    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.
    125,585 pointsBadges:
    report
  • graybeard52
    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.
    3,115 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following