Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008

    Unanswered: How to translate string with data have integer + character and null data


    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 :-

    ---------- ---------
    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
    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 ".


    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

  2. #2
    Join Date
    May 2003
    Look at the CAST function, and the COALESCE function. These are documented in the SQL Reference Vol 1.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2007
    Jena, Germany
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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