Query Builder give me an error for a case statement

[Christel]

 I am using Visual Studio 2008 and Telerik Reporting Q3 2008 SP2.  We are using MySQL as the database backend.


I have pasted this in the query builder, which I can run in MySql with no problems(when I substitute the parameters for real data):

SELECT     oc.GroupOffense, uc.category, SUM(CASE WHEN i.CommittedDt > @StartDtThisYear THEN 1 ELSE 0 END) AS ArrestsYTD,
                      SUM(CASE WHEN i.CommittedDt BETWEEN @StartDtLASTYEAR AND @EndDtLastYear THEN 1 ELSE 0 END) AS ArrestsLastYTD
FROM         analytics_incident i JOIN
                      analytics_IncidentCrimeInfo ci ON ci.incidentid = i.incidentid JOIN
                      ucroffensecategory uc ON uc.categoryid = ci.ucr_crime_category JOIN
                      ucroffensecode oc ON oc.categoryid = uc.categoryid
WHERE     oc.GroupOffense = @GroupAB
GROUP BY oc.groupoffense, uc.category

and when I click on Execute Query it gives me this error:
SQL Execution Error.
Executed SQL Statement : SELECT     oc.GroupOffense, uc.category, SUM(CASE WHEN i.CommittedDt > @StartDtThisYear THEN 1 ELSE 0 END) AS ArrestsYTD,
                      SUM(CASE WHEN i.CommittedDt BETWEEN @StartDtLASTYEA...
Error Source: MySql.Data
Error Message: Fatal error encountered during command execution.

The sql statement runs fine in mySql so why not in the report?

THanks
Christel

[Steve]

Hi Christel,

The Query Builder in our datasource wizard is part of Visual Studio and it works correctly against MS SQL database and T-SQL language, so other dialects of sql might cause problems or even not be supported. It would be best if you test the query outside of the VS environment against MS SQL and if it is valid, simply paste it in the "Choose Your DataBase Objects" step of the datasource wizard, without trying to execute it.

Sincerely yours,
Steve
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Christel]

This query runs fine in a MySql query browser.  I can also paste it into MS SQL query browser and it compiles fine so the syntax is OK.  Are you saying that Telerik does not support this kind of MySql syntax?

When I just try to bring up the command text in the properties tab of the table adapter, it gives me a

sql syntax error Encountered:
Error in SELECT clause: expression near 'END'.
Missing FROM clause.
Error in SELECT clause: expression near ','.
Error in SELECT clause: expression near 'FROM'.

[Svetoslav]

Hi Christel,

As described in Connecting Data to a Report help topic, Telerik Reporting does depend on a number of "standard" for the .NET framework data source interfaces. To connect to a specific database you should use an appropriate ADO.NET data provider which is responsible for all operations with this database. In the case of MySQL I guess one should use the MySQL Connector.

As far as the database support is concerned Telerik Reporting is limited to the number of databases that has appropriate ADO.NET data providers (which covers most of the popular databases).

Back to the problem. If I have understood you correctly, your query works fine in the MySQL query browser but you have hard time editing the MySQL data adapter. Unfortunately this is beyond our competence and the only thing I would suggest is to avoid using the data set designer.

As far as we recall we've already advised you in a response to a previous thread to use the data set designer to edit your query. But if you experience any troubles with the data set designer you still have a chance to edit the query (by hand). In the Solution Explorer locate your data set (the typed data sets files has .XSD extension), right click to open the context menu and choose the "Open With ..." option. From the Open With- dialog please select the "XML Editor" option and press the OK button. This will open the typed data set file in the XML editor instead in the data set designer. Locate the SelectCommand/DbCommand element where is the actual query and replace it with the new one (this is usually near the top of the file and the path should be something like this: DataSource/Tables/TableAdapter/MainSource/DbSource/SelectCommand/DbCommand). Saving the XSD file should case the underlying code to be re-generated and from now on the new query should take effect. I know this is not the best way to edit a typed data set but may save you in case you have hard time using the default Visual Studio Data Set designer.

Regards,
Svetoslav
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Christel]

I decided to use a view on in the database instead of trying to build the data I need in the report.

Thanks for your help anyway.
Christel