Hi,

I'm new in using DB2 9.1 by windows base.
I want to query the data that contain string then translate the string into integer using DB2.
The problems is If the data is null, i got the problem to translate.
How to translate string also allow null data to integer. If null data it will read as space.

My Data :-

GEOSEG_ID SEQNO
---------- ---------
329802 2
329803 3A
329805 1A
329806 10
329808 11A
329810 9
329811 4
329812 6
329813 5
329814 7
329815 8A
329843 13A
329844 20
329845 21
329846 19
329848 14
329849 16
329850 15
329851 22
329852 18
329854 24
329855 23
329868 NULL
329869 NULL
329870 NULL
329871 NULL
329872 NULL
329873 NULL


This is My Query :-
-----------------
Select geoseg.geoseg_id,CAST(LTRIM(RTRIM(TRANSLATE(Elot_d etail1.sequence, ' ', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))) AS INTEGER) AS seqNo
FROM GEOSEG,ELOT_DETAIL1
WHERE ELOT_DETAIL1.GEOSEG_ID = GEOSEG.GEOSEG_ID
ELOT_DETAIL1.POSTCODE = '41200'
AND ELOT_DETAIL1.BIT = '41'
GROUP BY GEOSEG.GEOSEG_ID,Elot_detail1.sequence




Sql error will appear like this :-
---------------------------------

SQL0420N Invalid character found in a character string argument of the
function "INTEGER". SQLSTATE=22018

SQL0420N Invalid character found in a character string argument of the function "INTEGER ".

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

Look at the CAST function, and the COALESCE function. These are documented in the SQL Reference Vol 1.

Also, for debugging purposes you should remove the cast and verify what exactly your conversion is producing. Naturally, you shouldn't have a character that's not a digit, decimal separator, and sign.