Cognos MySQL 10.2.1.1 | Changing from SQL Server

[derrickj]

Greetings,
I am prototyping with MySQL Cluster as a data source, previously SQL Server was the data source. When upgrading packages are there any known functions that are depreciated between the environments? I have an issue where SQL does not resolve and displays RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-69'.

[bdbits]

I am fairly certain there are SQL Server functions that MySQL does not have. It is not a matter of deprecation as much as different databases having different extended functionality beyond base ANSI SQL.

Is there a particular function causing you difficulty? We might be able to help you convert it to something MySQL supports.

[derrickj]

We have date functions which use the datepart and dateadd functuins.

Example:

((?DateRangeType? = '1') AND (?PreviousDatePrompt? = '18') AND ([Business Period Quarter] = datepart({quarter},dateadd({quarter},1,current_date))) AND ([Business Period Year] = datepart({year},dateadd({quarter},1,current_date)))
)
OR
((?DateRangeType? = '2') AND (?PreviousDatePrompt? = '18') AND ([Business Period Quarter] = datepart({quarter},dateadd({quarter},1,current_date))) AND ([Business Period Year] = datepart({year},dateadd({quarter},1,current_date)))
)

[bdbits]

Yes, datepart and dateadd are SQL Server specific functions. So you will need to update the expressions.

There are similar MySQL functions, like extract(), date_add(), date_sub(), etc.  There is quite a bit of info on converting between the two at http://www.w3schools.com/sql/sql_dates.asp.

Since you will be modifying the expressions anyway, you may want to consider using the Cognos built-in functions which will be database independent.
You can find them in the help files, or at http://www-01.ibm.com/support/knowledgecenter/SSEP7J_10.2.1/com.ibm.swg.ba.cognos.ug_cr_rptstd.10.2.1.1.doc/c_businesstimefunction.html%23BusinessTimeFunction?lang=en.