db2 cast problem

0
votes

[SQL]2010/12/07 20:18:32:184 : 0.0010 [update REG_COMP_DEF set OrderNo = Cast(Cast(SUBSTR(orderno,1,10) as numeric(10,0))+10 as varchar(10))||NVL(SUBSTR(orderno,10+1,length(orderno)-10),'') where length(OrderNo)>10 and OrderNo>='3000600050' and OrderNo like '300060%' and OrderNo not like '999999%'] com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -461, SQLSTATE: 42846, SQLERRMC: SYSIBM.DECIMAL;SYSIBM.VARCHAR

inner cast is ok

1
db2casting

1 Answers

0
votes

I can run it on my DB2 for i system (without the NVL(); not supported in my version).

Can you see if the outer cast will run when the inner one is cast to Decimal() instead of Numeric()?

ref: SQLState 42846 = "Cast from source type to target type is not supported."