Why error: 01428. 00000 - "argument '%s' is out of range" SQl Developer

Question

I have the following SQL script,

Select * From
(Select To_Char(Bmret.Pricedate, 'dd-mm-yyyy') As  Pricedate,  Bmret.Bmval, Bmret.id
, Cast(Exp(Sum(Ln(Cast(Bmret.Bmval As number))) Over (Partition By bmret.id)) As Number) As Twr
, RANK() OVER (PARTITION BY bmret.id ORDER BY bmret.pricedate asc) AS rank 
From Tab_A Bmret
Where 1=1
) B
Where 1=1
And B.Rank=1
;

, which provides me with the desired result of a column, twr, that contains the product of the elements in column Bmval across pricedates, grouped by id. However, I obtain the following error: 01428. 00000 - "argument '%s' is out of range".

I am aware that the error stems from the part Cast(Exp(Sum(Ln(Cast(Bmret.Bmval As number))) Over (Partition By bmret.id)) As Number) of the code and in particular that the "parameter passed into the function was not a valid value". Hence, my question is, is there any way to identify the id with values that are not valid?

I am not allowed to share the sample data. I am sorry. Thank you in advance. Best regards,

Answer 1

As @Kazi said, and as earlier answers had already mentioned, the issue is with using ln() with a negative number or zero. The documentation says:

LN returns the natural logarithm of n, where n is greater than 0.

so you can identify the IDs with out-of-range values with:

select id from tab_a where bmval <= 0

As you want the product of several numbers, you probably still want to include those values; but then having a zero amongst them should make the result zero, one negative number should make the result negative, two should make it positive, etc.

You can use the absolute value of your numbers for the calculation, and at the same time count how many negative values there are - then if that count of negatives is an odd number, multiply the whole result by -1.

Adapting the answer to your previous question, and changing the table and column names to match this question, that would be:

select to_char(a1.pricedate, 'dd-mm-yyyy') as pricedate, b1.bm, a1.bmval,
  round(cast(exp(sum(ln(cast(abs(a1.bmval) as binary_double))) over (partition by b1.bmik)) as number))
  *
  case
    when mod(count(case when a1.bmval < 0 then pricedate end) over (partition by b1.bmik), 2) = 0
    then 1
    else -1
  end as product
from tab_a a1
inner join benchmarkdefs b1 on (a1.id = b1.bmik);

db<>fiddle with a group that has two negatives (which cancel out), one negative (which is applied), and one with a zero - where the product ends up as zero, as you'd hopefully expect.

The point of the cast() calls was to improve performance, as noted in the old question I linked to, by performing the exp/ln part as binary_double; there is no point casting a number to number. If you don't want the binary_double part then you can take the casts out completely; but then you do also have to deal with zeros as well as negative values, e.g. keeping track of whether you have any of those too:

select to_char(a1.pricedate, 'dd-mm-yyyy') as pricedate, b1.bm, a1.bmval,
  round(exp(sum(ln(abs(nullif(a1.bmval, 0)))) over (partition by b1.bmik)))
  *
  case when min(abs(a1.bmval)) over (partition by b1.bmik) = 0 then 0 else 1 end
  *
  case
    when mod(count(case when a1.bmval < 0 then pricedate end) over (partition by b1.bmik), 2) = 0
    then 1
    else -1
  end as product
from tab_a a1
inner join benchmarkdefs b1 on (a1.id = b1.bmik);

db<>fiddle

For this query, which just gets values for the first date and product across all dates, that would translate (with casting) to:

select * from
(
  select to_char(bmret.pricedate, 'dd-mm-yyyy') as pricedate,  bmret.bmval, bmret.id
  , round(exp(sum(ln(abs(nullif(bmret.bmval, 0)))) over (partition by bmret.id)))
    *
    case when min(abs(bmret.bmval)) over (partition by bmret.id) = 0 then 0 else 1 end
    *
    case
      when mod(count(case when bmret.bmval < 0 then pricedate end) over (partition by bmret.id), 2) = 0
      then 1
      else -1
    end as twr
  , rank() over (partition by bmret.id order by bmret.pricedate asc) as rank 
  from tab_a bmret
) b
where b.rank=1

PRICEDATE	BMVAL	ID	TWR	RANK
11-08-2021	1	1	120	1
11-08-2021	12	2	524160	1
11-08-2021	22	3	-7893600	1
11-08-2021	1	4	0	1

db<>fiddle

As you were told in an old answer, if you don't want to see the (not very interesting) rank column then change select * from to select pricedate, bmval, id, twr from in the outer query.

You could also use aggregation with keep to avoid needing an inline view:

select to_char(min(pricedate), 'dd-mm-yyyy') as pricedate
, min(bmret.bmval) keep (dense_rank first order by pricedate) as bmval
, min(bmret.id) keep (dense_rank first order by pricedate) as id
, round(exp(sum(ln(abs(nullif(bmret.bmval, 0))))))
  *
  case when min(abs(bmret.bmval)) = 0 then 0 else 1 end
  *
  case
    when mod(count(case when bmret.bmval < 0 then pricedate end), 2) = 0
    then 1
    else -1
  end as twr
from tab_a bmret
group by bmret.id

PRICEDATE	BMVAL	ID	TWR
11-08-2021	1	1	120
11-08-2021	12	2	524160
11-08-2021	22	3	-7893600
11-08-2021	1	4	0

db<>fiddle

Answer 2

Please check the value of Cast(Bmret.Bmval As number). It must be greater than 0.

For further read:

https://www.techonthenet.com/oracle/functions/ln.php

Oracle / PLSQL: LN Function This Oracle tutorial explains how to use the Oracle/PLSQL LN function with syntax and examples.

Description The Oracle/PLSQL LN function returns the natural logarithm of a number.

Syntax The syntax for the LN function in Oracle/PLSQL is:

LN( number ) Parameters or Arguments number The numeric value used to calculate the natural logarithm. It must be greater than 0.

You need to define what will be the Ln(Cast(Bmret.Bmval As number)) if Bmret.Bmval <=0. If you define it as 0( which might not be correct for the calculation) then your query would be:

Select * From
(Select To_Char(Bmret.Pricedate, 'dd-mm-yyyy') As  Pricedate,  Bmret.Bmval, Bmret.id
, Cast(Exp(Sum(case when Cast(Bmret.Bmval As number)>0 then Ln(Cast(Bmret.Bmval As number)) else 0 end) Over (Partition By bmret.id)) As Number) As Twr
, RANK() OVER (PARTITION BY bmret.id ORDER BY bmret.pricedate asc) AS rank 
From Tab_A Bmret
Where 1=1
) B
Where 1=1
And B.Rank=1;