SQLException

[Roberto]

Hello, I've been using your Data Services wizard lately, but I came across some difficulties. It seems that the translation from the Rest query to the SQL has some problems, namely when I add more than one filter.

With each filter used separately it works perfectly, but combining the 2 throws the following exception:

Error executing query: Telerik.OpenAccess.RT.sql.SQLException: Incorrect syntax near the keyword 'IS'.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery()
   at OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute()
SQL:
SELECT COUNT(1) AS  EXPR1                  FROM [UnidadeAutonoma] a JOIN [PropriedadeHorizontal] AS b ON (a.[PropriedadeHorizontal_intIdPropriedadeHorizontal] = b.[intIdPropriedadeHorizontal]) JOIN [Pedido] AS c ON (a.[Pedido_intIdPedido] = c.[intIdPedido]) WHERE ((CASE WHEN ( (lower(b.[vcNumeroPH]) LIKE '%41%' ESCAPE '\' AND lower(c.[vcNumeroPedido]) LIKE '%03%' ESCAPE '\') IS NULL) THEN 0 ELSE  (CASE WHEN lower(b.[vcNumeroPH]) LIKE '%41%' ESCAPE '\' AND lower(c.[vcNumeroPedido]) LIKE '%03%' ESCAPE '\' THEN 1 ELSE 0 END)  END) = 1)   Telerik.OpenAccess.RT.sql.SQLException: Incorrect syntax near the keyword 'IS'.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery()
   at OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute()

The RESTful query is:
http://localhost:1635/WCFSIDataBase.svc/UnidadeAutonomas()?$filter=substringof('41',tolower(PropriedadeHorizontal/VcNumeroPH)) and substringof('03',tolower(Pedido/VcNumeroPedido))&$top=10&$expand=Pedido/Processo/ProcessoTipo, PropriedadeHorizontal, CadastroPredial&$inlinecount=allpages

Again, if only 1 of the filters is present it works fine (anyone).

Thanks in advance,

Roberto Félix

[Thomas]

Bom dia Roberto,

we identified the issue and the needed changes will be in the 2012 Q1 release that is due within 2 weeks.
Unfortunately, there is currently no workaround.

Regards,
Thomas
the Telerik team