to_char() 多了個字元

sp.param_value nvarchar2(200);

 

 

select sp.param_code, to_number(sp.param_value), sp.param_value,length(param_value)
  from sys_service_param sp
 where sp.param_type_code = 'bank'
   and sp.param_value = to_char(0.78,'0.00')

為什麼to_char不行呢？
select sp.param_code, to_number(sp.param_value), sp.param_value,length(param_value)
  from sys_service_param sp
 where sp.param_type_code = 'bank'
發現 param_value 字元長度為4  0.78=4

to_char 格式的是5個字元
SQL> select length(to_char(0.78,'0.00')),to_char(0.78,'0.00') from dual;
LENGTH(TO_CHAR(0.78,'0.00')) TO_CHAR(0.78,'0.00')
---------------------------- --------------------
                           5  0.78

不帶格式的是3個字元
SQL> select length(to_char(0.78)),to_char(0.78) from dual;
LENGTH(TO_CHAR(0.78)) TO_CHAR(0.78)
--------------------- -------------
                    3 .78

難怪查不到資料呢！！
SQL> select length('0.78') from dual;
LENGTH('0.78')
--------------
             4

而這種直接單引號就行

所以   and sp.param_value = trim(to_char(0.78,'0.00')) 就行了