Wednesday, September 29, 2010

Error while running burst report

So the scenario was as follows. I setup a report in report studio, set the burst option and tried to run it with options to email me the pdf files. Failed with the following messege : "The option to allow local processing has not been enabled. UDA-SQL-0267 Incompatible types for parameter number 1 in the function "_add_days". UDA-SQL-0268 The function "_add_days" must be processed by the database but the parameter "1" is not supported by the database." so I research and in my queries I had used the _add_days function twice, both after an underlying query ran a sql statement. At first I through well I need to turn on the limited local option but after that failed the thought came to my mind : Perhaps cognos doesnt like to intermix this function with its oracle counterpart. Thus I rewrote the logic into the sql query by itself as follows

instead of this in the detailed filter:

[trans_date] = _add_days (current_date,-1)

I wrote this as part of the actual sql query:

INV.TRANS_DATE between
To_Date(To_Char(SysDate - 1, 'MM/DD/YYYY'), 'mm/dd/yyyy') and
To_Date(To_Char(SysDate , 'MM/DD/YYYY'), 'mm/dd/yyyy')


I think this also should run faster since its touching the database and filtering the results directly there.

No comments:

Post a Comment