Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    5

    Unanswered: Convert varchar column to Decimal(8,3) and decimal (5,0)

    Convert varchar columns to Decimal(8,3) and decimal (5,0)


    I am using following query.

    Code:
    SELECT
    cast(PACKAGE_SIZE as decimal(8,3)) PACKAGE_SIZE,
    cast(PACKAGE_QTY as decimal(5,0)) PACKAGE_QTY
    FROM db.LNDG;
    HTML Code:
    Error:
    Error during SQLFetch
     22003(-413)[IBM][CLI Driver][DB2/AIX64] SQL0413N  Overflow occurred during numeric data type conversion.  SQLSTATE=22003
     (0.72 secs)

    My Data looks something like this

    PACKAGE SIZE : 00679000
    PACKAGE_QTY: 00001



    PACKAGE_SIZE in LNDG table is varchar(8)
    PACKAGE_QTY in LNDG table is varchar (5)


    I would like to make the query to handle nulls and default to "0" and also get rid of the above error message.
    Also i need to remove leading zeros.
    Appreciate help
    Last edited by siquadri; 11-14-16 at 20:17.

  2. #2
    Join Date
    Feb 2011
    Posts
    45
    Provided Answers: 1
    You are attempting to Cast as decimal (8,3), which means 8 digits total, of which 3 are to the right of the decimal point, leaving only 5 to the left of the decimal point. But your example is a number (679000) that has 6 digits to the left of the decimal--too big for the scale of your decimal number. Try decimal (9,3) instead. I just tested this, and changing 8 to 9 made it work for me.

  3. #3
    Join Date
    Dec 2016
    Posts
    7
    Hello,
    Use coalesce in order to replace nulls by zeroes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •