关于DECODE的用法

Question

select decode(length(max(substr(col_1_1_144_1,-5))+1),
1,'GDTEL_000000000000000000'||max(substr(col_1_1_144_1,-5))+1,
2,'GDTEL_00000000000000000'||max(substr(col_1_1_144_1,-5))+1) from 产品组客户组_产品客户组静态表;执行提示：ORA-01722：无效数字；select decode(length(max(substr(col_1_1_144_1,-5))+1),
1,'GDTEL_000000000000000000'||max(substr(col_1_1_144_1,-5))+1,
2,'2' from 产品组客户组_产品客户组静态表;执行成功；
select decode(length(max(substr(col_1_1_144_1,-5))+1),
1,'GDTEL_000000000000000000'||max(substr(col_1_1_144_1,-5))+1,
2,'a' from 产品组客户组_产品客户组静态表;执行失败。执行提示：ORA-01722：无效数字；
求教。。

Answer 1

max 后，再 to_char() 将数值转换为字符串　这样操作一下！

Answer 2

对于字符串oracle不支持+运算.只能用||进行连接.

Answer 3

select decode( length(max(substr(col_1_1_144_1,-5))+1), 
               1,'GDTEL_000000000000000000'||to_char(max(substr(col_1_1_144_1,-5))+1), 
               2,'a',
               '') as max_col_1
 from 产品组客户组_产品客户组静态表;-- 建议：去理解 decode 的用法！

Answer 4

--试试这个
select decode(length(max(substr(col_1_1_144_1,-5))+1),
1,'GDTEL_000000000000000000'||(max(substr(col_1_1_144_1,-5))+1),
2,'GDTEL_00000000000000000'||(max(substr(col_1_1_144_1,-5))+1)) from 产品组客户组_产品客户组静态表;