Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2006
    Posts
    31

    Unanswered: SQL to convert Char to Decimal

    Hi All,
    I have a column 'AMOUNT' defined as CHAR(18). Below is sample data and I need to find out the sum of the AMOUNT column. This column has negative numbers, nulls and decimal numbers. I tried to use CAST(AMOUNT AS DECIMAL) but erroring out with SQL0413N. Is there a way to avoid these errors?
    Thanks for your time advice.

    11374.00
    1.882138
    200
    25
    -
    565
    7.324088
    -42.97209
    854
    -
    6
    6
    -
    350
    -6
    15.380584
    1900
    90
    2166
    -77.976
    -
    90
    -77.976
    50
    92.4
    109.864973

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try adding the precision and scale to the cast as decimal. e.g. CAST(AMOUNT as DECIMAL(31,8)).

    Andy

  3. #3
    Join Date
    Nov 2006
    Posts
    31
    Andy,
    I have tried to use CAST(AMOUNT as DECIMAL(31,8)) but throwing the below error.
    SQL0420N Invalid character found in a character string argument of the function "DECIMAL". SQLSTATE=22018

    Sathya,
    Above is just a sample of records. This table has more than 48 Million records and I am using a multiple joins to filter the number of records.
    Last edited by samu78nyc; 11-05-09 at 10:48.

  4. #4
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    db2 values cast('12.34' as decimal(31,8))

    1
    ---------------------------------
    12.34000000

    1 record(s) selected.
    The casting is fine this way. Please Check value of "AMOUNT".

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    He has to use STRIP(AMOUNT) function before casting.

    Lenny

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    -413 OVERFLOW OCCURRED DURING NUMERIC DATA TYPE CONVERSION

    Explanation: During processing of the SQL statement, an overflow
    condition arose when converting from one numeric type to another. Numeric
    conversion is performed according to the standard rules of SQL.

    System Action: The statement cannot be processed. No data was retrieved,
    updated, or deleted.

    Programmer Response: Examine the syntax of the SQL statement to determine
    the cause of the error. If the problem is data-dependent, it might be
    necessary to examine the data processed at the time of the error.

    Lenny

    P.S. He can use: DOUBLE(STRIP(AMOUNT))
    Last edited by Lenny77; 11-05-09 at 11:39.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The problem is if any of the rows being processed has an invalid string, the error will be raised. What can be done is to wrap the conversion in a stored procedure where you define an exception handler. Then use a UDF that calls the stored procedure. Search for "soft_int" to find an example how to do this for integers.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up

    You can use this query, which is for select numeric data, only:

    Code:
    select column1 
    from 
    (select   case when substr(strip(column1), length(strip( column1)), 1) 
                                            in ('+', '-') 
                        then 
                        substr(strip(column1), length(strip( column1)), 1)  
                        || 
                        substr(strip(column1), 1, length(strip(column1)) - 1) 
                        when substr(strip( column1), 1, 1) in ('+', '-') 
                        then 
                        substr(strip(column1), 1, 1) 
                        ||  
                        strip(substr(strip(column1), 2)) 
                        else    strip(column1)  
                        end AS column1 
       from table1 
       where Translate(column1, ' ', '0123456789+-.') = ' ' 
         and  column1 > Translate(column1, ' ', '0123456789') 
    ) ii 
    where 
    replace( column1, ' ', '') = column1  
    and 
    length(replace( column1, '+', '')) >= length(column1) - 1 
    and 
    length(replace( column1, '-', '')) >= length(column1) - 1 
    and 
    length(replace( column1, '.', '')) >= length(column1) - 1 
    and 
    posstr(column1, '-')  in (0, 1) 
    and 
    posstr(column1, '+')  in (0, 1) 
    and not exists 
    (select * from sysibm.sysdummy1 
      where 
    posstr(column1, '-') > 0 
    and 
    posstr(column1, '+') > 0)
    You have to change "column1" to "amount" and "table1" on "your_table_name".

    Lenny

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can use ISNUMERIC UDF in Sample UDFs for Migration to filter out non-numeric values.

    Like this:
    SELECT amount FROM your_table WHERE ISNUMERIC(amount) = 0;
    Last edited by tonkuma; 11-05-09 at 15:53.

Posting Permissions

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