How to delete junk value records from a PF

10 pts.
Tags:
AS/400
Physical File
SQL
Hi, I have a file which has a date field of DECIMAL 8 and it has value +++++++++++ for some records. How can I delete these records via SQL or programatically.

Software/Hardware used:
SQL, RPGLE, AS400

Answer Wiki

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

Try selecting in SQL statement where “field” = 0 or 0101001 I have seen both of these show up as +++++++ when attempting to display the field as a date

You can also use DSPPFM (display physical file member) use F24 F10 F11 shift right until you get to the field starting position in the record to see what the actual value is in the field

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
    ...I have a file which has a date field of DECIMAL 8...   For clarity, we should note that "date field" and "DECIMAL 8" are contradictory. A DECIMAL 8 column is not a "date field". Such a field can hold values that represent dates, but they are not actual "DATE" values. But all of that is just for background and might not be directly related to your problem.   It's not clear what your problem is. A DECIMAL 8 field cannot contain "++++++++".   However, some reporting function might only display "++++++++" when you try to display or print the value in the column. What are you doing when you see the values? What is the exact function you are running?   Tom
    125,585 pointsBadges:
    report
  • MDratwa
    You can write a simple program.  You should be able to read the record and check the field for non-numeric and update those records with zero or nulls (if field is set up for nulls).
    785 pointsBadges:
    report
  • TomLiotta
    The first thing that should be determined is whether errors are in the data or in the presentation of the data. A 'DECIMAL 8' definition implies a SQL table, so the errors should not be in the data. There should be no "fix" that would be done by a program.   However, it's possible that 'DECIMAL 8' is not the actual definition. It could be a '8P 0' field instead, and the OP didn't know the original definition. In that case, a program might be a fix.   But even with a program, the actual problem first needs to be understood. Not necessarily by us, but by the OP.   Tom
    125,585 pointsBadges:
    report
  • BigKat
    just as an FYI: the +++++++ can also show up if your SQL session is set to display dates as *YMD (any 2 digit year format) and the date is outside of the calendaring window (1940-2039 I think) 
    8,350 pointsBadges:
    report
  • TomLiotta
    As BigCat mentions, the attempt to present a value as a DATE can give the result as plus signs. It might be a perfectly valid numeric value, yet not be correct as a "date". That's why we need to know exactly what the circumstance was. -- Tom
    125,585 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