Calculation (sql sysdate) 1

[CognosTalk]

What can be the problem with this statement
I created a calculation inside the framework manager and it does not work. i only use rhe sysdate function.
I use Oracle 10g

CASE when to_number(to_char({sysdate},'mm')) < 04 then to_char(to_number(to_char({sysdate},'yyyy')))-1||' '||to_char({sysdate},'yyyy')

when to_number(to_char({sysdate},'mm')) >= 04 then to_char({sysdate},'yyyy')||'-'||to_char(to_number(to_char({sysdate},'yyyy'))+1) end





Thank you in advance for your support,
Mila.

 

[blom0344]

Alternative:

CASE when extract(month,{sysdate}) < 4 then to_char(to_number(to_char({sysdate},'yyyy')))-1||' '||to_char({sysdate},'yyyy') 

else to_char({sysdate},'yyyy')||'-'||to_char(to_number(to_char({sysdate},'yyyy'))+1) end

Oracle supports implicit conversions, which means that you can probably simplify to:

CASE when extract(month,{sysdate}) < 4 then to_char({sysdate},'yyyy')-1||' '||to_char({sysdate},'yyyy') 

else to_char({sysdate},'yyyy')||'-'||to_char({sysdate},'yyyy')+1 end

Ties Blom

 

[CognosTalk]

Ties, non of the statements are working in framework manager.
i get UDA-SQL-0219 The function "to_char" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.

 

[blom0344]

Set interface type to OR (instead of OL) and set query processing to Limited local..

Ties Blom

 

[CognosTalk]

i am not sure if i would like to set my process to limited local.is it nessesary for this type of calculation?
can you kindly explain how do i set an interface type?

 

[blom0344]

You set the interface type in the properties of the connection. (activate through View --> properties.)
Cognos will give you an indication if you can use a certain function as database only..


Ties Blom

 

[CognosTalk]

Ties, i am still experiencing the same error
my interface type was always OR (i guess by default) but i changed to local processing, and this calclulation does not work, the same error.

 

[blom0344]

I suspect that Cognos evaluates the extract function as a Cognos type and then runs into the Oracle to_char with negative results.
So, skip my advice on using the extract function and use your original expression (making sure that you use a integer 4 instead of 04.
Does that give proper result?

Ties Blom

 

[CognosTalk]

it did not worked. not sure what is wrong.
maybe i have to look for alternative solution. any thoughts?

 

[blom0344]

The following expression works fine at my site:

CASE 
WHEN extract(month;current_date) <4
then cast(extract(year;current_date)-1;varchar(4)) ||'-'|| cast(extract(year;current_date);varchar(4))
WHEN extract(month;current_date) >=4
THEN cast(extract(year;current_date);varchar(4)) ||'-'|| cast(extract(year;current_date)+1;varchar(4))
else null end

Ties Blom

 

[CognosTalk]

Ties, it worked!!!!
Thanks you so very much, i just substituted the ; with , that was it!!!!

 

[blom0344]

Yep, with my design language the separator is ; for english it should be ,

Ties Blom

 

[CognosTalk]

Ties, you will not believe it , but the calculation does not work properly in the report studio.
I created thuis caclulation as a standaalone calculation and published within the package that i am using.
It complains about the cast format. i am using it in the filter of the report and below is the error that i get:

UDA-SQL-0458 PREPARE failed because the query requires local processing of the data. The option to allow local processing has not been enabled. UDA-SQL-0481 An expression or operator (cast) is not supported by the database. This operation requires local processing of the data. RSV-SRV-0042 Trace back:

What can be done besides eallowing local processing?

 

[blom0344]

What a drag..
When I perform a legitimate subtraction of 2 dates against an Oracle database, then Cognos decides to switch to local processing (on the cognos server).
This is a 100% native Oracle expression and yet it is not allowed to be handled by the database itself.

Anyway, what is wrong with activating local processing?

Ties Blom

 

[CognosTalk]

Ties, can you kindly explain me + and - of local processing?
I think it can affect performance.

 

[blom0344]

database processing will in all cases give the best performance, cause the Cognos server will only have to pass the results. Cognos support does not offer much information, other than that the processing of non SQL99 compliant statements would need limited local processing.
Very good reference can be found here:

http://www.oreilly.com/catalog/sqlnut/chapter/ch04.html

Ties Blom