ORA-01722鸟食

Question

我有以下查询，这是工作在"Oracle SQL Developer“。

在BIRT Eclipse中，我得到了ORA-01722 "not a number“错误。

使用TO_NUMBER()转换数字没有任何帮助。你有什么想法吗？

select INSTR_NR, VALID_FROM, PRICE, return, sqrt(ema)* 2.326347874041 as VaR1d
from 
(select INSTR_NR, VALID_FROM, PRICE from

(select DATA_IDC, ATTR_IDC, VALUE as INSTR_NR 
from DLEATTRDAT 
where ATTR_IDC = 'FUND:fund:index_abbi_ref'
and LOCKSTATE = 0) x left join
(select DATA_IDC, VALID_FROM, VALUE as PRICE
from DLEATTRDAT 
where ATTR_IDC = 'FUND:instr:EUR_price') y
on x.INSTR_NR = y.DATA_IDC)

model
         partition by (INSTR_NR)
         dimension by (VALID_FROM)
         measures (PRICE,0 return,0 ema)
         rules
         ( return[any] order by VALID_FROM
           = nvl2
             ( PRICE[cv()-1]
             ,  POWER ( ln ( PRICE[cv()] / (PRICE[cv()-1]) ), 2)
             , POWER (ln ( PRICE[cv()] / (PRICE[cv()-3]) ), 2) )
             ,
            ema[any] order by VALID_FROM
           = nvl2
             ( return[cv()-1]
             ,  .06 * return[cv()] + .94 * ema[cv()-1]
             ,  return[cv()]  ) )  
   order by INSTR_NR, VALID_FROM desc 

Answer 1

从您的查询中，我得出结论，DLEATTRDAT表或视图中的列值是一个VARCHAR2。在查询中，有时将其用作文本值(INSTR_NR)，有时将其用作数值(价格)。

简而言之，这是一个数据模型设计缺陷(我可以根据经验判断，我们的应用程序中也有类似的问题)。

在后面的查询中，您将在数值计算中使用价格。

不幸的是，查询优化器可能选择在之前或执行数值计算之后评估过滤器限制where ATTR_IDC = 'FUND:instr:EUR_price'。

这是这种“一刀切”的表格中的常见陷阱。

在您的例子中，优化器似乎选择了首先应用WHERE子句，然后执行转换，但是当从BIRT调用时，优化器以相反的方式进行了转换，从而产生了ORA-01722。

这既不是BIRT的错误，也不是数据库的错误；这是查询和我们人类思考Oracle DB应该如何工作的方式的问题。

幸运的是，有一些方法可以在不重新处理数据模型的情况下处理此问题。

AFAIK WITH子句通常会强制优化器临时存储结果，因此处理此问题的一种方法可能是逻辑上等效但技术上不同的查询：

WITH x as (
  select DATA_IDC, ATTR_IDC, VALUE as INSTR_NR 
  from DLEATTRDAT 
  where ATTR_IDC = 'FUND:fund:index_abbi_ref'
  and LOCKSTATE = 0
),
y as (
  select DATA_IDC, VALID_FROM, VALUE as PRICE
  from DLEATTRDAT 
  where ATTR_IDC = 'FUND:instr:EUR_price'
)
select INSTR_NR, VALID_FROM, PRICE, return, sqrt(ema)* 2.326347874041 as VaR1d
from 
(select INSTR_NR, VALID_FROM, PRICE 
 from x left join y
 on x.INSTR_NR = y.DATA_IDC)

model
         partition by (INSTR_NR)
         dimension by (VALID_FROM)
         measures (PRICE,0 return,0 ema)
         rules
         ( return[any] order by VALID_FROM
           = nvl2
             ( PRICE[cv()-1]
             ,  POWER ( ln ( PRICE[cv()] / (PRICE[cv()-1]) ), 2)
             , POWER (ln ( PRICE[cv()] / (PRICE[cv()-3]) ), 2) )
             ,
            ema[any] order by VALID_FROM
           = nvl2
             ( return[cv()-1]
             ,  .06 * return[cv()] + .94 * ema[cv()-1]
             ,  return[cv()]  ) )  
   order by INSTR_NR, VALID_FROM desc 

看看执行计划。如果不是创建SYS_xxxxx内联视图，优化器提示可能会有所帮助。

根据DLEATTRDAT表的大小，新查询可能比原始查询慢一点。

无论如何，您的查询中还有另一个bug :您正在使用从VARCHAR2到NUMBER的隐式转换，因为您在数值计算中使用了价格。

因此，您应该进一步修改可能的查询，并在适当的位置添加显式TO_NUMBER。