Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    3

    Question Unanswered: Weird CASE behaviour (SQL0420 - character in CAST argument not valid)

    Hi,

    I'm new to DB2 and I ran into the following problem trying to convert some (Oracle) DECODE functions to CASE:

    Consider this statement:

    update HERONDERZ_5412 CCT set
    OPDRCODE_OMS=(select substr(OMS,1,24) from KODERINGEN_5410
    where SRTTAB= CASE CCT.OPDRCAT
    when 1 then 12
    when 2 then 13
    when 3 then 14
    when 4 then 74
    when 5 then 77
    else 0
    end
    and dec(CODE)=CCT.OPDRCODE)

    Results in a "SQL0420 - Character in CAST argument not valid"

    In my test situation the table being updated only contains the value 2 for CCT.OPDRCAT.

    So I tried the following statement:

    update HERONDERZ_5412 CCT set
    OPDRCODE_OMS=(select substr(OMS,1,24) from KODERINGEN_5410
    where SRTTAB=13
    and dec(CODE)=CCT.OPDRCODE)

    And (surprisingly enough) this works just fine.

    Each of the (relevant) columns have the following datatypes:

    SRTTAB NUMERIC(4)
    CODE VARCHAR(10)
    OPDRCAT NUMERIC(1)
    OPDRCODE NUMERIC(2)

    And I'm using DB2 UDB for iSeries V5R1, by the way.

    If anyone could explain what's causing the error it'd be greatly appreciated.

    TIA.

    Roelof

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Just a guess (I'm not familiar with your version) ...

    OPDRCAT NUMERIC(1)

    If the size (1) refers to the length and not the byte size, could it not be falling over attempting to cast an int of length 1 to an int of length 2?

  3. #3
    Join Date
    Apr 2003
    Posts
    3
    I don't know..

    I tried the same query on our Oracle(9i) server, and it works just fine (provided I replace the dec() funtion by to_number()).

    This leads me to believe that the statement is syntacticly correct, but that something inside DB2's 'black box' is messing things up.

    I ran the statement over an ODBC connection to our AS/400 server and only got the SQL0420 error.

    When I log on the the server and run the statement from the commandline I get this:

    CPD4002: Key mapping error in field *N member KODER00001.
    CPD4002: Key mapping error in field *N member KODER00001.
    ...
    < the above about 30 times, followed by : >
    SQL0420: Character in CAST argument not valid.

    Maybe this rings a bell with someone, but to me it might just as well have been written in Swahili.

    Any suggestions on how to fix this problem (or work around it) would be appreciated.

    Thanks.

    Roelof

  4. #4
    Join Date
    Apr 2003
    Posts
    3
    Well, I managed to find a solution. So, in case someone runs into a similar problem:

    update HERONDERZ_5412 CCT set
    OPDRCODE_OMS=(select substr(OMS,1,24) from KODERINGEN_5410
    where dec(CODE)=CCT.OPDRCODE
    and SRTTAB=cast(CASE CCT.OPDRCAT
    when 1 then 12
    when 2 then 13
    when 3 then 14
    when 4 then 74
    when 5 then 77
    else 0
    end as numeric(2)))

    ^- this works.

    Why I have to cast a numeric value to a numeric value, I have no idea (and it seems rather pointless to me), but this solved my problem.

    Still, if anyone can clear up this behaviour, then by all means..

Posting Permissions

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