Results 1 to 8 of 8
  1. #1
    Join Date
    May 2009
    Posts
    3

    Unanswered: Intermediate type cast error : Char to int

    In normal sql query , while converting char to int for comparison some times db shows type caste error , the same query is running fine on other database.

    But if we do the other way round, if we type cast integer to char for comparison its working fine at all the places.

    Is there some parameter in the DB that might be leading to this. Any pointer to the solution would be really helpful

    Thanks
    Raj

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    no....
    because all no. (5)>('5') are char , but not all char ('A' )<> (A) are integer
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    May 2009
    Posts
    3
    Thanks for the reply rahul but iIn this we are sure that the selected records will be integer type only, as a matter of fact the query is running on the other db env that we have.

    It gives generic type cast exception.

  4. #4
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    you must be having ' ' or ',' or '.' or any other char somewhere in the data
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  5. #5
    Join Date
    May 2009
    Posts
    3
    Hi,

    No this is also not the scenario, the data set on which we are operating doesn't have any of these. The basic question is why this is working over one db everything same and why not on other.

    The data set remains the same in both the db. Is there any env specific variable that might impact this.

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Can you give an example of a record where the type cast exception happens?
    It also helps if we know which platform & version of DB2 you are using.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Choudhary.raj
    Hi,

    No this is also not the scenario, the data set on which we are operating doesn't have any of these. The basic question is why this is working over one db everything same and why not on other.

    The data set remains the same in both the db. Is there any env specific variable that might impact this.
    You have some differences in the data for sure. Try to find that by narrowing down the error on a few rows only.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can use a UDF to find the data causing error.
    Although the UDF might not complete, it will be usefull to narrow down the error.

    Sample UDF:
    Code:
    --
    -- Description: Returns 0 when the input string is a valid integer or decimal representation,
    --              otherwise returns > 0.
    --
    --              Validity test cases.
    --                Test 1  : All characters are '0123456789.-+' or blank.
    --                Test 2  : No other characters exists left of '-' or '+' except blank(s).
    --                Test 3-1: Number of '-' or '+' should be 0 or 1.
    --                Test 3-2: Number of '.' should be 0 or 1.
    --                Test 4  : No blanks are allowed between characters except after leading '-' or '+'.
    --                Test 5  : It should have at least one digit character.
    --
    
    CREATE FUNCTION ISNUMERIC (Source VARCHAR(40))
     RETURNS INTEGER
     LANGUAGE SQL
     DETERMINISTIC
     NO EXTERNAL ACTION
    RETURN
    CASE 
    WHEN translate(source,'','0123456789.-+') <> ''                    THEN 10
    WHEN posstr(ltrim(source),'-') > 1
      OR posstr(ltrim(source),'+') > 1                                 THEN 20
    WHEN length(rtrim(ltrim(translate(source,'','0123456789.')))) > 1  THEN 31
    WHEN length(rtrim(ltrim(translate(source,'','0123456789-+')))) > 1 THEN 32
    WHEN posstr(ltrim(rtrim(translate(source,'','-+'))),' ') > 0       THEN 40
    WHEN translate(source,'','.-+') = ''                               THEN 50
    ELSE 0
    END
    Example of use of the UDF:
    Code:
    SELECT c1
         , ISNUMERIC(c1)
      FROM table_name
     WHERE ISNUMERIC(c1) > 0
     FETCH FIRST 10 ROWS ONLY
    ;
    Last edited by tonkuma; 06-02-09 at 08:27.

Posting Permissions

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