| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
11-05-09, 08:59
|
|
Registered User
|
|
Join Date: Nov 2006
Posts: 31
|
|
|
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
|
|
11-05-09, 09:21
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
Try adding the precision and scale to the cast as decimal. e.g. CAST(AMOUNT as DECIMAL(31,8)).
Andy
|
|
11-05-09, 09:40
|
|
Registered User
|
|
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 09:48.
|
11-05-09, 10:24
|
|
Registered User
|
|
Join Date: Sep 2008
Location: Toronto,Canada
Posts: 606
|
|
Quote:
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".
|
|
11-05-09, 10:33
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
He has to use STRIP(AMOUNT) function before casting.
Lenny
|
|
11-05-09, 10:36
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
-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 10:39.
|
11-05-09, 11:26
|
|
Registered User
|
|
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
|
|
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
|
|
11-05-09, 12:38
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
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
|
|
11-05-09, 14:11
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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 14:53.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
SQL to convert Char to Decimal
