Not able to parse year conversion of Oracle dates

[blom0344]

For a typical Framework the connection can only be Oracle. So, for adding dimensions like year I am trying to define new objects that hold the year part of a date , like:

to_number(to_char(datefield,'YYYY'))

However, this gives typical error messages like :

Code Select Expand


GEN-ERR-0015 Initially, in data source type(s) 'OL', function 'fn:to_char' is not supported in 'RelationalQueryProvider'. After decomposition, in data source type(s) 'OL', function 'fn:to_char' is not supported in 'RelationalQueryProvider'.


Anyone any idea what is wrong? What does OL mean any way?

Thanx..

[COGNOiSe administrator]

Can't you just use year(datefield) ?

[blom0344]

Yes, that seems to parse. Which leads to the question why it is not listed in the function oversight.
Is there a known issue with using native database functions within the framework?

[blom0344]

A quick check in the cognos Knowledge base pointed to the connection type. When you change the type from 'OL' to 'OR' native functions are working as they should. This seems to be only possible within the Framework itself.

[COGNOiSe administrator]

Meesa wonder if OL stands for ORACLE OLEDB ...

Anyhow, if there is a native function missing, you can always add it.

[blom0344]

jA-jA, You are right about the OLEDB..

[MDXpressor]

Isn't the Oracle function called Extract?

Extract(year,[datefield])

[blom0344]

Yep, but in that case it is

Code Select Expand

Extract(Year from [datefield])

In my case my reference-point is still Oracle 7 (cause I'm really a DB2 guy), and I have to catch up with Oracle9 and 10 enhancements.

Interestingly, Oracle8i does not document the  Extract function, but it seems to work okay in 8.1.7.

Thanks for the tip anyway..