Results 1 to 4 of 4

Thread: CAST a Decimal

  1. #1
    Join Date
    Apr 2007
    Posts
    63

    Question Unanswered: CAST a Decimal

    A QUICK QUESTION.

    Is it not possible to cast a decimal(12) to decimal(7,2)?

    Its throwing -413 error.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Not if the number exceeds 9999999.99

    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Yes, if the values was less than 100000.

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT d                        AS "decimal(12)"
         , CAST(d AS decimal(7,2) ) AS "decimal(7,2)"
      FROM (VALUES CAST(99999.9 AS decimal(12)) ) AS d(d)
    ;
    ------------------------------------------------------------------------------
    
    decimal(12)    decimal(7,2)
    -------------- ------------
            99999.     99999.00
    
      1 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    SELECT d                        AS "decimal(12)"
         , CAST(d AS decimal(7,2) ) AS "decimal(7,2)"
      FROM (VALUES CAST(100000. AS decimal(12)) ) AS d(d)
    ;
    ------------------------------------------------------------------------------
    
    decimal(12)    decimal(7,2)
    -------------- ------------
    SQL0413N  Overflow occurred during numeric data type conversion.  
    SQLSTATE=22003
    
    <snipped some lines>
    
     sqlcode :  -413 
    
     sqlstate :  22003

  4. #4
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    basically your decimal(12) can hold much more data than decimal(7,2) and since you are narrowing down [ i.e. going from a higher container decimal(12) to a lower sized container decimal(7,2) ] you cannot input anything more than 9999999.99 for then it i'll be something like trying to pour water from a 2 liter jug into a 1 liter jug; obviously there'll be an overflow

    one thing you can try out is if value is less than 9999999.99 then convert else handle error
    case (when value < 9999999.99)
    then CAST
    else
    HANDLE ERROR
    end
    IBM Certified Database Associate, DB2 9 for LUW

Posting Permissions

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