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

[Roel29]

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

 

[MarcLodge]

Hi,

Have you tried changing the CASE statement to a series of WHERE....AND.....(OR......AND.......) as in:

where (SRTTAB = 12
and CCT.OPDRCAT = 1)
or (SRTTAB = 13
and CCT.OPDRCAT = 2)
etc.

which is what I think your SQL is trying to achieve.

Marc

 

[Dagon]

Your statement:

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

will only pick out the matches with SRTTAB = 13. The CODES on these rows are probably OK. But your original statement also brings back rows for STRTAB = 12 etc. Try going through each of the options e.g.

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

where XX is 12, then 14, then 77 etc. You should eventually find which set of rows is causing the error.

Alternatively, you could just look for any invalid CODES using something like:

select * from KODERINGEN_5410
where translate(code, '', '0123456789') <> ''