Results 1 to 6 of 6

Thread: cast error

  1. #1
    Join Date
    Dec 2013
    Posts
    4

    Unanswered: cast error

    hi,

    i am tried to convert the datatype of column HP_DTL_CYCLE_DAY_OF_MONTH(varchar (2)) to decimal(5,0) in DB2 but getting the error.

    Invalid character found in a character string argument of the function "DECIMAL".. SQLCODE=-420, SQLSTATE=22018, DRIVER=4.16.53


    i am using
    cast(HP_DTL_CYCLE_DAY_OF_MONTH as decimal(5,0))

    the source table having datatype varchar(2) Null.

    Please help me on this.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What was the value of HP_DTL_CYCLE_DAY_OF_MONTH?

    Invalid character found in a character string argument of the function "DECIMAL"..
    All blank(s) or zero length string are also invalid character when cast to a numeric datatype.

  3. #3
    Join Date
    Dec 2013
    Posts
    4
    the data is coming as.i got this by using distinct on column

    01
    03
    04
    05
    06
    07
    08
    10
    11
    12
    13
    15
    17
    18
    19
    20
    21
    22
    25
    26
    27

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    None of those values that you posted will produce that error from the Cast() that you posted. There must be either different data or different code to produce that error message.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Hargun,
    You have either a SPACE or a NULL value in some row of this table.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by hargun View Post
    the data is coming as.i got this by using distinct on column

    01
    03
    04
    05
    06
    07
    08
    10
    11
    12
    13
    15
    17
    18
    19
    20
    21
    22
    25
    26
    27
    What was the result of this query?
    Code:
    SELECT COUNT(DISTINCT
                 HP_DTL_CYCLE_DAY_OF_MONTH
           ) AS cnt_distinct_values
         , COUNT(DISTINCT
                 NULLIF(HP_DTL_CYCLE_DAY_OF_MONTH , '')
           ) AS distinct_values_not_blanks
         , COUNT(CASE HP_DTL_CYCLE_DAY_OF_MONTH
                 WHEN '' THEN
                      0
                 END
           ) AS count_blank_string
     FROM  <your table>

Posting Permissions

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