CAST trouble
Brian
This SQL was running under ORACLE and used the CONVERT function.
Amount is defined as CHARACTER 16 in the table.
select amount from table where cast(amount as DECIMAL(16,2)) < 100.00;
I get this error.
SQL0420N Invalid character found in a character string argument of the
function "DECIMAL". SQLSTATE=22018
Knut Stolze
What is in the "amount" column? It seems that you do not have a valid
repreentation for decimal numbers in there:
$ db2 "? sql0420"
SQL0420N Invalid character found in a character string argument
of the function "<function-name>".
Explanation:
The function "<function-name>" has a character string argument
that contains a character that is not valid in a numeric SQL
constant. The function may have been called as a result of using
the CAST specification with "<function-name>" as the target data
type. The function or data type used in the SQL statement may be
a synonym for "<function-name>".
If a decimal character is specified in the DECIMAL function then
that is the character that must be used in place of the default
decimal character.
User Response:
Ensure that the character strings that are being converted to
numeric types contain only characters that are valid in numeric
SQL constants, using the decimal character, if specified.
sqlcode : -420
sqlstate : 22018
If you use a comma for the decimal character, then you can do this:
SELECT amount
FROM table
WHERE DECIMAL(amount, 16, 2, ',') < 100.00;
If it is a period sign, then use '.' as decimal character or whatever else you
have used in the original string.
--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Serge Rielau
It seems like you have either non-digits or more than one decimal point
or such...
Cheers
Serge
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Toronto
Visit DB2 Developer Domain at
http://www7b.software.ibm.com/dmdd/
Paul Vernon
news:f489f822.03030...@posting.google.com...
Just guessing, but maybe you have strings in your amount column that are not
valid string representations of decimals?
Because I'm a generous sort, here is a UDF to help you out.
gen it with
db2 -td@ -f file_name
the run
SELECT * FROM table WHERE F.IS_DECIMAL(amount) = 'N'
Enjoy!
P.S. The func does not check that the scale and precision are in range, but
you could mod the func to do that if req.
Paul Vernon
Business Intelligence, IBM Global Services
CREATE FUNCTION F.IS_DECIMAL (X VARCHAR(4000))
RETURNS CHAR(1)
LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC
BEGIN ATOMIC
DECLARE i INTEGER;
DECLARE l INTEGER;
DECLARE c CHAR(1);
DECLARE y VARCHAR(4000);
SET y = RTRIM(LTRIM(X));
SET l = CASE WHEN LOCATE('.',y) = 0 THEN LENGTH(y) ELSE LOCATE('.',y) -1 END;
SET i = 1;
SET c = 'Y';
loopA:
WHILE i <= l
DO
IF SUBSTR(y, i, 1) NOT BETWEEN '0' AND '9'
THEN
SET c = 'N';
-- RETURN c;
LEAVE loopA;
ELSE
set i = i + 1;
END IF;
END WHILE loopA;
SET i = i + 1;
SET l = LENGTH(y);
loopB:
WHILE i <= l
DO
IF SUBSTR(y, i, 1) NOT BETWEEN '0' AND '9'
THEN
SET c = 'N';
-- RETURN c;
LEAVE loopB;
ELSE
set i = i + 1;
END IF;
END WHILE loopB;
RETURN c;
END
@
COMMENT ON FUNCTION F.IS_DECIMAL IS
'Returns ''Y'' if the input string is a valid representation of a DB2
DECIMAL.I.e. it can be cast to DECIMAL without a runtime error'
@
Brian
The amount column is a character field and can have nothing in it.
Therefore trying to convert an empty string into decimal representation would
not work.
Knut Stolze <sto...@de.ibm.com> wrote in message news:<b47cto$lv3$1...@fsuj29.rz.uni-jena.de>...
PM (pm3iinc-nospam)
?
PM
PM (pm3iinc-nospam)
;-)
PM
Vesa Nopanen
news:KVW9a.5580$KJ3.8...@news20.bellglobal.com...
> db2 select coalesce(cast(nullif(col1, '') as decimal(16,2)), 0) from tdl06
I don't have any trouble with
db2 select coalesce(cast(nullif('', '') as decimal(16,2)), 0) from
sysibm.sysdummy1
Maybe your col1 is not compatible with empty string?
It might work differently on different platforms. I tested with with Windows
DB2 UDB 7.1. fixpak 5.
Vesa Nopanen
Brian
Thanks a lot to everyone for the suggestions.
"Vesa Nopanen" <nop...@quartal.com> wrote in message news:<b49n8r$me1$1...@news1.songnet.fi>...
Benjamin Doyle
I was searching the ibm-db2 group for examples of how to
create a scalar function, and discovered yours--
CREATE FUNCTION F.IS_DECIMAL (X VARCHAR(4000))
RETURNS CHAR(1) LANGUAGE SQL CONTAINS SQL
NO EXTERNAL ACTION DETERMINISTIC
BEGIN ATOMIC
DECLARE i INTEGER;
DECLARE l INTEGER;
DECLARE c CHAR(1);
DECLARE y VARCHAR(4000);
from a response dated 2003-03-06.
It promised to solve some problems but
I'm unable to generate a function using the BEGIN ATOMIC ....END
My test is admittedly a very simple one---
CREATE FUNCTION TESTING (X CHAR)
RETURNS VARCHAR(20)
LANGUAGE SQL CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE Y VARCHAR(20);
SET Y = X;
RETURN Y;
END!
DSNT408I SQLCODE = -104, ERROR: ILLEGAL
SYMBOL "ATOMIC". SOME SYMBOLS
THAT MIGHT BE LEGAL ARE: ON
<INTEGER>
We have DB2 v7.1 installed. Is the environment that
I'm attempting to create the function different than yours?
Any comments?
sincerely
Ben Doyle
Serge Rielau
Try this:
db2 -td%
connect to <mydb>%
create function .......%
Serge Rielau
don't know the command line tool there .....
Benjamin Doyle
.
I tried changing the delimiter as suggested, both in spufi and my db2
mgmt tool dbxpert. Curiously, I can create (my first) stored procedure
with the BEGIN ATOMIC .... END, but then the SP can't be found in the
current path when I try to execute it, and I'm getting past what I'm
familiar with.
The SQL REF doesn't refer to any BEGIN ATOMIC .... END but it would be
very helpful if I could use it.
Ben
Serge Rielau
to a single statemenet (like DB2 V7.1 for UWO).
Benjamin Doyle
I'm still unable to get the BEGIN ATOMIC clause accepted.
PM (pm3iinc-nospam)
PM