SQL TRANSLATE hates me!

[tomholden]

this SQL works perfectly for finding numerics:

select TRANSLATE(cuno, ' ', '0123456789,.# ' ),cuno
 FROM tmccd42.arcreditassignments​ 

this SQL fails due to "Argument 2 of function TRANSLATE not valid." with the preceding message "CPD4318-Derived operands not valid for operator Reason code 14. 14 -- Operand number two is not valid for the operation."

select TRANSLATE(right(trim(h.doccode),2), ' ', '0123456789,.# ' )
 from codad42dta.oas_dochead h​ 

can someone help me figure out my boneheaded mistake? i'm sure i'm missing something simple or i'm losing my mind, not sure which at this point!

[B.Hauser]

Hmm, the second statement works for me without any problem. Is there something special in your h.doccode column?
What release you are on? Are you on the current PTF-Level?

[tomholden]

Hmm, the second statement works for me without any problem. Is there something special in your h.doccode column?
What release you are on? Are you on the current PTF-Level?

v7r4 we are behind on CUM PTFs though unfortunately(we are hosted so i no longer have the ability to stay up to date on patches ). the only difference between the columns is that the h.doccode is a varchar column and the cuno is a char column

[vazymimil]

I can reproduce your problem using CCSID 1208 :

this one works

values translate(cast('abc1' as varchar(10) ccsid 37), ' ', '0123456789,.# ' )

this one does not and fails with CPD4318 reason code 14

values translate(cast('abc1' as varchar(10) ccsid 1208), ' ', '0123456789,.# ' )

So this should work I think

select TRANSLATE(cast(right(trim(h.doccode),2) as varchar(2) ccsid 37), ' ', '0123456789,.# ' )
 from codad42dta.oas_dochead h;

[tomholden]

I can reproduce your problem using CCSID 1208 :

this one works

values translate(cast('abc1' as varchar(10) ccsid 37), ' ', '0123456789,.# ' )

this one does not and fails with CPD4318 reason code 14

values translate(cast('abc1' as varchar(10) ccsid 1208), ' ', '0123456789,.# ' )

So this should work I think

select TRANSLATE(cast(right(trim(h.doccode),2) as varchar(2) ccsid 37), ' ', '0123456789,.# ' )
from codad42dta.oas_dochead h;

that worked perfectly! thanks!!!