ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL Error Code -406. Fails in RPG. Works in SQL Window

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

  • SQL Error Code -406. Fails in RPG. Works in SQL Window

    I am running SQL in an RPG, inserting rows into an IBM file, and getting this error -406

    A numeric value is out of range.
    A CALCULATED OR DERIVED NUMERIC VALUE IS NOT WITHIN THE RANGE OF ITS OBJECT COLUMN


    However, if I paste the code into Navigator and insert into the same IBM file, it runs without error.

    And if I run it with results to a query window and order by the numeric fields, I don't see anything that looks wrong.

    Any Thoughts ??

    EDIT: SOLVED
    Last edited by MFisher; January 20, 2022, 10:15 AM.

  • #2
    Check the values you want to insert. It seems you try to insert numeric values that are larger than the maximum value you can insert in a column.
    Could it be some values are not initialized correctly?
    May be blank instead of a numeric value?

    Comment


    • #3
      Does the table have calculated columns? The error message seems to imply that. If so, look at what other columns the calculated columns use and see what the calculated result would be.

      Did you look at messages in the job log? If it is related to message SQL0406 (which it may or may not) then it should give you more information as to what column it is and what type of error it is.

      Comment


      • #4
        What is an IBM file?

        Comment


        • #5
          The problem is this CASE Statement, highlighted section, but I don't understand why. I am checking some Numeric fields to determine if I should insert a 'Y' or 'N' into the field in my output file, which allows NULLs
          The output field in the file is defined as 1 A . I have no problem inserting with a SQL query from Client Access Qry Window. Fails when the SQL is executed within RPG.

          LMLCCD is packed 8,0 and contains dates in YYYYMMDD format, or may be 0
          WCYCI is packed 3,0

          Code:
          CASE
          WHEN LMLCCD = 0 then 'Y'
          [COLOR=#c0392b][B]WHEN LMLCCD > 0 and WCYCI > 0 and (Days(Current_Date ) -
               Days(Digits(LMLCCD) concat '000000')) > (365 / WCYCI) then 'Y'[/B][/COLOR]
          WHEN LMLCCD > 0 and WCYCI > 0 and (Days(Current_Date ) -
               Days(Digits(LMLCCD) concat '000000')) <= (365 / WCYCI) then 'N'
          WHEN WCYCI = 0 then 'Y' ELSE '?'
          END
          Q) "...What is an IBM file?..."

          A) Regular DDS defined file, not SQL Table
          Last edited by MFisher; January 20, 2022, 09:43 AM.

          Comment


          • #6
            This result may be 4 positions, such as 2,764 days between Current_Date and LMLCCD
            Code:
            (Days(Current_Date ) - Days(Digits(LMLCCD) concat '000000'))
            While WCYCI is 3,0 and the result of 365/ WCYCI is a result like 30.41666666

            Is RPG SQL getting upset that I am comparing an int 4,0 result with a 2,x numeric result ?

            EDIT: SOLVED
            Changed code to CAST as INT. I guess Client Access SQL is "smart" enough to do the conversion itself
            Code:
            CASE WHEN LMLCCD = 0 then 'Y'
            WHEN LMLCCD > 0 and WCYCI > 0 and (Days(Current_Date ) -
            Days(Digits(LMLCCD) concat '000000'))
            > [COLOR=#c0392b][B]cast(round((365 / WCYCI),0) as int)[/B][/COLOR] then 'Y'
            WHEN LMLCCD > 0 and WCYCI > 0 and (Days(Current_Date ) -
            Days(Digits(LMLCCD) concat '000000'))
            <= [B][COLOR=#c0392b]cast(round((365 / WCYCI),0) as int)[/COLOR] [/B]then 'N'
            WHEN WCYCI = 0 then 'Y'
            ELSE '?' END
            Last edited by MFisher; January 20, 2022, 10:16 AM.

            Comment

            Working...
            X