sql - 带有超出范围消息的日志函数返回

Question

全部。我有以下代码。

with filDelta as(select a.* from delta a
Where a.Pdelta>0
And a.oldprice between 0.0001 and 10000 
and a.new_closeD between 0.0001 and 10000
),

Incr as(
select  a.Symbol, log(a.oldprice,a.New_closeD) increase
From filDelta a
)

Select  a.symbol 
from Incr a
Where a.increase>=1.3459

但是，它在 log() 所在的行上返回错误。错误是

ORA-01428: parameter'0' out of range 01428. 00000 - "argument '%s' is out of range"

但是在我的 with 子句中，我已经过滤了值，请问这是为什么呢？

Answer 1

一些数据表明simulates the problem ，看起来 Oracle 优化器正在以一种将谓词推到不同级别的方式重写查询，这意味着您的过滤器在计算日志后被应用。您可以使用 /* no_push_pred */ 提示来避免这种情况。

但是您也可以在调用日志之前将条件捕获在同一级别:

with filDelta as(
  select a.* from delta a
  where a.Pdelta>0
  and a.oldprice between 0.0001 and 10000 
  and a.new_closeD between 0.0001 and 10000
),
Incr as(
  select a.Symbol,
    case when a.New_closeD <= 0 then null
      else log(a.oldprice,a.New_closeD) end increase
  From filDelta a
)
Select a.symbol 
from Incr a
where a.increase>=1.3459;

SQL Fiddle .

或者 CTE 级别较低:

with filDelta as(
  select a.*
  from delta a
  where a.Pdelta>0
  and a.oldprice between 0.0001 and 10000 
  and a.new_closeD between 0.0001 and 10000
)
select a.Symbol
from filDelta a
where case when a.New_closeD <= 0 then null
  else log(a.oldprice,a.New_closeD) end >=1.3459;

[SQL Fiddle](http://sqlfiddle.com/#!4/85ac0/10).

或者根本没有 CTE:

select a.Symbol
from delta a
where a.Pdelta>0
and a.oldprice between 0.0001 and 10000 
and a.new_closeD between 0.0001 and 10000
and case when a.New_closeD <= 0 then null
  else log(a.oldprice,a.New_closeD) end >=1.3459;

SQL Fiddle .