I am new using DB2 9.1 database 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 :-
Expand|Select|Wrap|Line Numbers
- 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
-----------------
Expand|Select|Wrap|Line Numbers
- Select geoseg.geoseg_id,CAST(LTRIM(RTRIM(TRANSLATE(Elot_detail1.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
---------------------------------
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