Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368

    Unanswered: Change column from CHAR to SMALLINT

    v10 z/OS

    I have a table with CHAR column and need to change it to SMALLINT and unload/reload data.
    How can I verify that only numbers are stored in this CHAR column? Please suggest some query.


    Thanks

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    110
    Provided Answers: 13
    select count(1) from mytab where cast(mycharcol as smallint)=0
    Regards,
    Mark.

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    db2 "create table test (c1 int, c2 char(3))"
    DB20000I The SQL command completed successfully.

    db2 "insert into test values (111,111)"
    DB20000I The SQL command completed successfully.

    db2 "insert into test values (222,'b2b')"
    DB20000I The SQL command completed successfully.

    db2 "select * from test"

    C1 C2
    ----------- ---
    111 111
    222 b2b

    db2 select count(1) from test where cast(c2 as smallint)=0

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


    The error message tells me that c2 contains some char.

    How can I select rows where c2 is not all numbers? In my example, I want the query to display the 2nd row

    Thanks

  4. #4
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    110
    Provided Answers: 13
    Quote Originally Posted by db2girl View Post
    ...
    How can I select rows where c2 is not all numbers? In my example, I want the query to display the 2nd row
    select c2
    from test
    where length(trim(translate(trim(c2), '*', ' 0123456789')))>0
    Regards,
    Mark.

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    Thank you, Mark

Posting Permissions

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