sql/400 convert 9,0 to 4,0 getting cast argument not valid

40 pts.
Tags:
SQL/400
we have full ss# in our employee master and now HR is wanting to stip the first 5 digits off. Trying: update clocfilemh/ckempeff set eessno = decimal(substr(char(eessno),6,4),4,0) getting "Character in CAST argument not valid." When I try select eessno, decimal(substr(char(eessno),6,4),4,0) getting "column sel0002 contains replacement character +." How can I convert a field to the last 4 digits? TIA

Software/Hardware used:
os/400

Answer Wiki

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

Discuss This Question: 5  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
    How can I convert a field to the last 4 digits?   You need to find which records have leading zeros or have non-numeric digits. If this file has been written to or updated with anything but SQL, it can have data that isn't valid for SQL.   Correcting those conditions will take two different methods. And if you prove that neither condition exists, much deeper analysis will be needed.   Tom
    125,585 pointsBadges:
    report
  • shumatev
    original column is numeric 9,0 so not possible to have non-numeric digits. 
    40 pointsBadges:
    report
  • TomLiotta
    ...so not possible to have non-numeric digits.   Yes, it is. The data definition does not determine what data can be written to the field. It determines what data should be written. Review the CRTRPGMOD FIXNBR() parameter and ask yourself why that parameter exists if non-numeric data cannot be found in a numeric field.   Note that this is true of native I/O, but not of SQL. Native I/O verifies data when a 'read' access is done. SQL verifies when 'write' access is done, e.g., for INSERT or UPDATE. So native I/O can write invalid data and generally won't see it until it's read back in again. SQL won't write invalid data, so it should never get into a file.   The basic reason for this in the AS/400 line goes back to System/36 files and similar files that did not have external definitions. It gets pretty complicated, but non-numerics can easily get into numeric columns.   Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    if Tom is correct the error occurs when you try to convert back to decimal.  Try this  select eessno, substr(char(eessno),6,4) then if it runs try to find the error select eessno, substr(char(eessno),6,4) where   substr(char(eessno),6,1) not between '0' and '9' or  substr(char(eessno),6,2) not between '0' and '9' or  substr(char(eessno),6,3) not between '0' and '9' or  substr(char(eessno),6,4) not between '0' and '9'
    49,590 pointsBadges:
    report
  • philpl1jb
    Correct that .. here is the test to find bad values... select eessno, substr(char(eessno),6,4) where substr(char(eessno),6,1) not between ’0′ and ’9′ or substr(char(eessno),7,1) not between ’0′ and ’9′ or substr(char(eessno),8,1) not between ’0′ and ’9′ or substr(char(eessno),9,1) not between ’0′ and ’9′
    49,590 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