Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    12

    Unanswered: casting of integer

    Hi All,

    I am trying to cast a character value to an integer in my select statement and running into problems. For values > 1000 a comma is present and this creates the problem.
    The SQL that I ran and the error message I got are as follows,

    db2 => select cast(dispensed_quantity as int) from shyam.scot_lhcc_pres_jan

    1
    -----------
    240
    120
    500
    SQL0420N Invalid character found in a character string argument of the
    function "INTEGER". SQLSTATE=22018

    The 4th value in the column has a value of 1,305. Hence, the comma is not being identified as a valid character and I am getting the above error message. I tried with bigint, smallint, real and double but to no luck.

    Can anybody please help me out with this and let me know of any other way to achieve this?

    Thanks,
    Shyam.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: casting of integer

    See if this helps

    db2 "select int(replace('1,234',',',''))+100 as success from sysibm.sysdummy1"

    SUCCESS
    -----------
    1334

    1 record(s) selected.

    Cheers

    sathyaram

    Originally posted by shyams75
    Hi All,

    I am trying to cast a character value to an integer in my select statement and running into problems. For values > 1000 a comma is present and this creates the problem.
    The SQL that I ran and the error message I got are as follows,

    db2 => select cast(dispensed_quantity as int) from shyam.scot_lhcc_pres_jan

    1
    -----------
    240
    120
    500
    SQL0420N Invalid character found in a character string argument of the
    function "INTEGER". SQLSTATE=22018

    The 4th value in the column has a value of 1,305. Hence, the comma is not being identified as a valid character and I am getting the above error message. I tried with bigint, smallint, real and double but to no luck.

    Can anybody please help me out with this and let me know of any other way to achieve this?

    Thanks,
    Shyam.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2003
    Posts
    12

    Re: casting of integer

    I am not sure as to how this works. But, I replaced '1,234' with the colname and my SQL looked like this,

    select int(replace(dispensed_quantity,',',''))+100 as success from scot_lhcc_pres_jan

    and I got the same error message again.

    Shyam.

    Originally posted by sathyaram_s
    See if this helps

    db2 "select int(replace('1,234',',',''))+100 as success from sysibm.sysdummy1"

    SUCCESS
    -----------
    1334

    1 record(s) selected.

    Cheers

    sathyaram

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: casting of integer

    Can you go in stages ...

    First do
    select dispensed_quantity,char(replace(dispensed_quantity ,',',''),10) from scot_lhcc_pres_jan fetch first 100 rows only

    See if this removes the ','s in every dispensed_quantity ... If not, which are the ones and what is the differnece between the ones there are no commas and the once which still have commas in them



    Cheers
    Sathyaram


    Originally posted by shyams75
    I am not sure as to how this works. But, I replaced '1,234' with the colname and my SQL looked like this,

    select int(replace(dispensed_quantity,',',''))+100 as success from scot_lhcc_pres_jan

    and I got the same error message again.

    Shyam.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Aug 2003
    Posts
    12
    When I did a step-by-step operation of the SQL, I realised that some of the values had decimals and those were the ones creating problem. So I changed the query to convert the value arising after "replace" into decimal with the required precision and scale.

    Thanks a lot, Sathya. Your help is sincerely appreciated.

    Shyam.

Posting Permissions

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