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