SqlCeCommand does NOT support subqueries ?

Question

yesterday I wanted to create a sql query ( using system.data.sqlce ) which return values from diff tables on mobile devices.

My scenario is as follows: - sql table: Xtable (fields: name : is string, mat : is string, state : is int value); - XTable contains 100 records my sql statement is:

SELECT name, (SELECT count(*) from Xtable where state=0) as Marked, count(*) as Total
FROM XTable
GROUP BY name

result: 0 rows, because i have error with second SELECT.

IF I execute following sql statement:

SELECT name, count(*) as Total FROM XTable GROUP BY name
result : > 0 rows.

Sqlcecommand not support multiselect query? how can I fix it ?

Answer 1

If you can give error details then it would be easy to help you.

(SELECT count() from Xtable where state=0)

No argument in count()

(SELECT count(*) from Xtable where state=0)

Edit

In your edit you have * as argument in count. It was missing originally, is it correct.

Answer 2

SQL Compact does not support this type of subquery. See this MSDN Forum thread for confirmation.