1.当需要用到between-and的字段是varchar2类型时,放在where 之后会报错:
ORA-01722:无效数字
select *
from table_name
where num between 15and 17;
select * from (
select to_number(num) asnum
from table_name
)
where num between 15and 17;
解决:在不改变表结构的情况下,只能建立临时表存放数据,操作临时表;
2. 放在select-from 中case-when,无问题,且不需要to_number转换:
select (case when num between 0 and 10 then 'A'
when num between 11 and 20 then 'B'
else 'C' end ) from table_name;