ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Need help replacing the plus symbols "++++++++++++++++++" to 0

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

  • Need help replacing the plus symbols "++++++++++++++++++" to 0

    Is it possible to replace the ''+++++++++++++++++++++++++++++" symbols from a field to 0 ?

    SELECT LRLREF, LRVALR, LRCLIR, LRDTAA, LRDTMM, LRDTJJ, LRNTMC,
    REPLACE(LRNTMC,'+++++++++++++++++++++++++++++',0) X
    FROM LIB1.TABLE1 WHERE LRLREF = '1'

    The result from the query:

    Click image for larger version

Name:	ResultFile.jpg
Views:	404
Size:	81.5 KB
ID:	153084
    I appreciate any help.

  • #2
    If this is from STRSQL, the ++++++ symbols do not mean that there are +++ symbols in the table. Instead, they mean that the data wasn't valid or was in error.

    For example, if you have a numeric field that has non-numeric characters (including blanks) it will show up with + signs, or a calculation was invalid such as divide by zero, invalid date in a date field, data was in a CCSID that couldn't be converted, etc.

    The solution would be to fix the data so that it is valid for the column type.

    Comment


    • #3
      Thank you

      Comment


      • #4
        It could be a null value.
        In that case, try with COALESCE(LRTNMC, 0).

        COALESCE selects the first non null value from a list.

        NVL is exactly the same (less to type!)

        Comment


        • #5
          It could also be that the column has a data type that cannot be displayed in STRSQL query output, e.g. XML or BLOB.

          Comment


          • #6
            I tried COALESCE for null value but its not null.

            Is it possible to identify the invalid value without guessing?

            Comment


            • #7
              It could help if you give us more info: What data type the column is?

              You could use DSPPFM to view the data.
              First search the record with problems (you can use RRN() to get the relative record number, and the you can go directly to it in DSPPFM).
              Once in DSPPFM, press F10 and then F11.
              And you will be able to see the data directly.

              Comment


              • #8
                The data type is numeric(22,3)

                Comment


                • #9
                  Trapping invalid numeric data is a little tricky with SQL. Since you cannot enter any invalid numeric values in SQL Tables (contrary to DDS physical files).
                  Quite often these numeric columns include *Blanks.
                  What you can do is the following:
                  1. Run your SELECT Statement and read all rows (I'd suggest to do it with ACS Run SQL Script instead of the Green Screen STRSQL)
                  2. Invalid numeric values cause an Joblog Entry with the message id CPF5035.
                  3. With the following query you can get the schema, table, field which includes an invalid numeric value and the relative record no (of the row including invalid numeric values
                  Code:
                  Select Cast(Trim(Substr(Message_Tokens,  1, 10)) as VarChar(10))  File,
                            Cast(Trim(Substr(Message_Tokens, 21, 10)) as VarChar(10))  Library,
                            Cast(Trim(Substr(Message_Tokens, 31, 10)) as VarChar(10))  Field,
                            BigInt(Substr(Hex(Message_Tokens), 81, 8))                 RRN,
                            Cast(Trim(Substr(Message_Second_Level_Text, 57))
                                                                      as VarChar(256)) Text
                     From Table(QSYS2.JobLog_Info('*')) x
                     where MESSAGE_ID = 'CPF5035';
                  4. With this information you are now able to fix all the invalid numeric values, with the method of your choice (for example SQL)

                  Birgitta

                  Comment


                  • #10
                    Thank you Birgitta that was helpful.

                    Comment

                    Working...
                    X