CODEWITHSUNDEEP
eclipseoracle-databasebirt
Helmuth Rieger
Helmuth Rieger

Reputation: 1

ORA-01722 birt eclipse

I have following query which is working in "Oracle SQL Developer".

In BIRT Eclipse I get the ORA-01722 "not a number" error.

Converting the numbers with TO_NUMBER() didn't help. Do you have any ideas?

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

Upvotes: 0

Views: 279

Answers (1)

hvb
hvb

Reputation: 2668

From your query I conclude that the column VALUE in the DLEATTRDAT table or view is a VARCHAR2. In your query, your are using this sometimes as a text value (INSTR_NR) and sometimes as a numeric value (PRICE).

To put things short, this is a data model design flaw (I can tell from experience, we had similar problems in our application).

Later in your query, you are using PRICE in a numerical calculation.

Unfortunately, the query optimizer may choose to evaluate the filter restriction where ATTR_IDC = 'FUND:instr:EUR_price' before or after performing the numerical calculation.

This is a common pitfall in this kind of one-size-fits-all tables.

In your case it seems the optimizer chose to apply the WHERE clause first, then do the conversion, but when called from BIRT the optimizer did it the other way round resulting in the ORA-01722.

This is neither an error withing BIRT nor in the DB; it's a problem of the query and the way we humans think about how the Oracle DB should work.

Fortunately, there are ways to handle this without re-working the data model.

AFAIK the WITH clause usually forces the optimizer to temporary store the results, so one way to handle this might be the logically equivalent, but technically different query:

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

Look at the execution plan. If it's not creating a SYS_xxxxx inline view, an optimizer hint might help.

Depending on the size of the DLEATTRDAT table, the new query may be a bit slower than the original query.

Anyway, there's yet another bug in your query: You are using implicit conversion from VARCHAR2 to NUMBER, because you are using PRICE in a numerical calculation.

So you should further modify may query and add an explicit TO_NUMBER at the appropriate place(s).

Upvotes: 1

Related Questions