sponsored links

Collapse

Announcement

Collapse
No announcement yet.

Numeric field with Invalid data

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Numeric field with Invalid data

    Hi all,
    I've a file created with DDS, that contains some invalid numeric data in numeric field (es. blank in numeric column).

    Is there a way to identify this records and force zero with an update?

    Many thanks.

  • #2
    IMO, that's the #1 reason to not use DDS PFs.

    My fix is always to use RPG. Read every row, checking for decimal data errors. On error, clear the field and update.

    Comment


    • #3
      I have never found a good way to find these. Be aware this can be an implict cast as well. For example if you compare Order no (numeric) to a another value that is char, an implict cast occurs to make the compare work. Why it can't at least give a field name I don't know.
      jtaylor's method works if the file has bad data, but does not find where cast or where clause has a compare issue.

      Comment


      • #4
        You could use the control specification keyword
        FIXNBR(*ZONED *INPUTPACKED)
        in a program to fix the error.

        But if this is a reoccurring error I would locate the program(s) that causes the error.

        Regards
        Peder

        Comment


        • #5
          SQL cannot handle invalid data in numeric values. Because SQL assumes the data is correct. BTW it is not possible to enter invalid numeric data in SQL tables (not even if you try a CPYF with *NOCHK).
          If you know there are blanks in numeric fields, you may use a circumvent with SQL, i.e. convert the numeric column into hex an then search through the hex value for blanks.

          Select RRN(a), Hex(ColumnX)
          from YourTable a
          Where Hex(ColumnX) like '40%';

          You also could use regular expressions for seaching the Hex Value.

          Birgitta

          Comment


          • #6
            Originally posted by B.Hauser View Post
            SQL cannot handle invalid data in numeric values. Because SQL assumes the data is correct. BTW it is not possible to enter invalid numeric data in SQL tables (not even if you try a CPYF with *NOCHK).
            If you know there are blanks in numeric fields, you may use a circumvent with SQL, i.e. convert the numeric column into hex an then search through the hex value for blanks.

            Select RRN(a), Hex(ColumnX)
            from YourTable a
            Where Hex(ColumnX) like '40%';

            You also could use regular expressions for seaching the Hex Value.

            Birgitta
            Hi all,
            with HEX it works.
            Many thanks.

            Bye

            Comment

            Working...
            X