none
There was an error parsing the query - SELECT COUNT(DISTINCT)

    Question

  • Why am I getting this error with CE 3.5? 


    There was an error parsing the query. [ Token line number = 1,Token line offset = 14,Token in error = DISTINCT ]

    CowEventsTableAdapter.Connection.Open()
    cmd = New SqlServerCe.SqlCeCommand("SELECT Count(DISTINCT CowID) FROM CowEvents WHERE FarmID=" & GlobalsClass.intFarmID & " AND EventMonth = " & DatePart(DateInterval.Month, Today) & " And EventDay = " & DatePart(DateInterval.Day, Today) & " And EventYear = " & DatePart(DateInterval.Year, Today) & "AND EventType ='Routine'" & " AND Complete =1", CowEventsTableAdapter.Connection)
    reader = cmd.ExecuteReader()
    While reader.Read()
    intRoutine = reader.GetValue(0)
    End While
    CowEventsTableAdapter.Connection.Close()
    Tuesday, September 30, 2008 9:20 AM

Answers

  • Yes, you are right, Distinct cannot be used inside Count. However there is a workaround for the same.

     

    Since, v3.5 we support nested queries in from clause, so a query like this will solve your purpose:

    select count(*) from (select distinct cowid from ..... ) t

    Tuesday, September 30, 2008 12:41 PM
    Answerer
  • there was actually a very easy workaround for this:



    strSQL = "SELECT DISTINCT CowID from CowEvents WHERE EventType='Routine' AND Complete=1"
    cmd = New SqlServerCe.SqlCeCommand(strSQL, CowEventsTableAdapter.Connection)
    reader = cmd.ExecuteReader()
    Do Until reader.Read = False
                intRoutine = intRoutine + reader.FieldCount
    Loop



    Monday, October 13, 2008 6:22 PM

All replies

  • CE Doesnt support DISTINCT inside the Count() Function???? I read it on another forum via my searching... that cannot be right!
    Tuesday, September 30, 2008 9:30 AM
  • Yes, you are right, Distinct cannot be used inside Count. However there is a workaround for the same.

     

    Since, v3.5 we support nested queries in from clause, so a query like this will solve your purpose:

    select count(*) from (select distinct cowid from ..... ) t

    Tuesday, September 30, 2008 12:41 PM
    Answerer
  • thanks for the workaround... I am messing somethign up tho:

    There was an error parsing the query. [ Token line number = 1,Token line offset = 23,Token in error = select ]

     cmd = New SqlServerCe.SqlCeCommand("select count(*) from (select distinct cowid from CowEvents  WHERE FarmID=" & GlobalsClass.intFarmID & " AND EventMonth = " & DatePart(DateInterval.Month, Today) & " And EventDay = " & DatePart(DateInterval.Day, Today) & " And EventYear = " & DatePart(DateInterval.Year, Today) & "AND EventType ='Routine'" & " AND Complete =1 GROUP BY CowId", CowEventsTableAdapter.Connection)
           
    Thursday, October 02, 2008 7:16 PM
  • Yeah, from your command it seems that the query you wrote was not complete.

     

    You have forgot to close the inner query with a right parenthesis. Also, even after adding a right paranthesis, you require an alias for the nested query.

     

    Let's say you want to get distinct items from Table1 based on c1 which has columns c1, c2 and c3. Query should look like:

    select count(*) from (select distinct c1 from Table1 where c2 = 'Value1' and c2 = 'Value2') t;

     

    There is no need to have a group by when you already have distinct (if you just want a single value with all of row count).

     

    Try something like this (I am not sure of your schema here):

     cmd = New SqlServerCe.SqlCeCommand("select count(*) from (select distinct cowid from CowEvents  WHERE FarmID=" & GlobalsClass.intFarmID & " AND EventMonth = " & DatePart(DateInterval.Month, Today) & " And EventDay = " & DatePart(DateInterval.Day, Today) & " And EventYear = " & DatePart(DateInterval.Year, Today) & "AND EventType ='Routine'" & " AND Complete =1) MyTable", CowEventsTableAdapter.Connection)

    Friday, October 03, 2008 5:23 AM
    Answerer
  • thanks!    However, Im still getting an error.  Am I supposed to do something else where it says "MyTable"?
    Friday, October 03, 2008 3:57 PM
  • You have to close the inner query with a Right Parenthesis. MyTable is just an alias for the table source denoted by inner query. If you don't specify it, you will get errors. Another thing, if you use any unnamed columns (like count(*) etc) in the inner query, they need an alias.

     

    What's the error you are getting now?

    Can you do one small thing, when running your application put a breakpoint on the step where the CommandText for SqlCeCommand is set, so that we can see the final t-sql generated after you variables are populated.

     

    The usual problem that one sometimes encounters is while generating such t-sql statements, let's say if you have a single quote in a string variable that you use to construct t-sql, it will cause trouble with parsing.

    You should either switch to using parameterized queries or make sure that the variables you use have each single quote ' replaced by two single quotes. XYZ.Replace("'", "''")

     

    Monday, October 06, 2008 5:57 AM
    Answerer
  • There was an error parsing the query. [ Token line number = 1,Token line offset = 27,Token in error = select ]

    I dont understand those error messages.  How does one "read" them?

    CommandText = "select count(CowID) from (select distinct cowid from CowEvents  WHERE FarmID=1 AND EventMonth = 10 And EventDay = 6 And EventYear = 2008 AND EventType ="Routine"  AND Complete =1) MyTable"



    Monday, October 06, 2008 4:32 PM
  • Let's go by your error message:

    Try to paste your whole T-SQL in a notepad and disable word-wrap.

    When you see this error: There was an error parsing the query. [ Token line number = 1,Token line offset = 27,Token in error = select ]

     

    This means that the parser of t-sql noticed some wrong structure in the t-sql. Where did it notice, from parameters it says, line number 1 and offset 27 and token as select, which means it is not able to understand the inner query.

    Are you sure you are firing the queries against SQLCE 3.5 or 3.5 SP1, because the nested query in from clause works only in that.

     

    Secondly I see another problem in the query, Routine should be in single inverted commas rather than double inverted commas.

     

    Just check this and let me know. If it still is causing issues, I will require a small repro-application with the database from you to check the problem on my side.

    Tuesday, October 07, 2008 5:56 AM
    Answerer
  • Still happens:

    There was an error parsing the query. [ Token line number = 1,Token line offset = 27,Token in error = SELECT ]

    CommandText = "SELECT COUNT(CowID) from (SELECT DISTINCT CowID from CowEvents WHERE FarmID=1 AND EventMonth=10 And EventDay=7 And EventYear=2008 AND EventType='Routine' AND Complete=1) MyTable"

    If I go to the "connection properties" of the database properties:
    Product Name: Microsoft SQL Server Compact Edition
    Product Version: 3.0.5300.0

    so, I take it this is v3.0 not v3.5?  If so, does this mean I need to completely re-do my DB again?  I just converted it over from SQL Express.  I dont understand how it wouldnt be 3.5 as I am nearly positive that is what I just downloaded last week.




    Tuesday, October 07, 2008 4:31 PM
  • Yes, you are working against version 3.1 of SQL Server Compact. It does not support such queries.

     

    Make sure you are working against the right version.

    VS2005 SP1/SQL Server 2005 SP2 - SQL CE 3.1

    VS2008 - SQLCE 3.5

    VS2008 SP1/SQL Server 2008 - SQLCE 3.5 SP1

     

    SQL Server Compact 3.5 SP1 will have assembly version of 3.5.1.0 and the build number should be 5692.00. It is also available separately on Microsoft Download Center.

     

    Coming to your second question. No, you don't have to re-do your DB again. Version 3.5 and 3.5 SP1 come with Upgrade API (SqlCeEngine.Upgrade) to help upgrade your older version database to the new one, this query should work fine then.

     

    Let me know if this solves your problem.

    Ravi

    Wednesday, October 08, 2008 6:33 AM
    Answerer
  • so since I am using VS2005 SP1 I am screwed.  From searching, I found there are some work arounds to getting 3.5 to work with VS2005, but I loose designer support.  This is insanely frustrating.  I had to change by DB from SQL 2005 Express to CE because as of SP3 Express is no longer supported on XP Tablet PC edition.  So my app worked fine, until SP3 was installed on the test tablet.  I convert everything over to CE, only to find out that I must either build some crazy complex Query to perform the same function as a simple COUNT DISTINCT, implement 3.5 & loose designer support, or upgrade to VS2008. 

    it feels like MS is trying to make my life difficult... 


    Wednesday, October 08, 2008 5:07 PM
  • there was actually a very easy workaround for this:



    strSQL = "SELECT DISTINCT CowID from CowEvents WHERE EventType='Routine' AND Complete=1"
    cmd = New SqlServerCe.SqlCeCommand(strSQL, CowEventsTableAdapter.Connection)
    reader = cmd.ExecuteReader()
    Do Until reader.Read = False
                intRoutine = intRoutine + reader.FieldCount
    Loop



    Monday, October 13, 2008 6:22 PM
  • Help me.
    I have sql ce 3.5 but i get this error : [Token line number = 1,Token line offset = 56,Token in error = select] in query select path_to_name, name from Maps where open_date = (select max(open_date) from Maps);
    Friday, February 05, 2010 7:04 AM
  • Inline SELECT is not supported in SQL Compact.

    First you must execute:

    select max(open_date) from Maps;

    and then use the result as the parameter in a second query:

    select path_to_name, name from Maps where open_date = ?


    http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.
    • Proposed as answer by ananasus Saturday, February 06, 2010 11:58 AM
    Friday, February 05, 2010 9:13 AM
    Moderator
  • Why  sql ce return string with NULL if table is empty? MySQL dont return  string if table is empty. How can i do sql ce dont return NULL if table is empty? 
    Saturday, February 06, 2010 11:38 AM
  • Could you provide a sample of this?

    (Table definition and SQL statement)
    http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.
    Saturday, February 06, 2010 12:10 PM
    Moderator
  • there:http://s004.radikal.ru/i205/1002/04/fe841b7747d2.jpg
    table dont contant strings  but return NULL,NULL,NULL.

    i write c#-programm and there
    DataBaseOperations dbo;
    SqlCeDataReader dr1 = (dbo=new DataBaseOperations()).SendSelectQuery("select max(open_date) from Maps;");
                    if (dr1.Read())
                    {
                        param.Value = dr1[0];
                        mapIsEmpty = false;
                    }
                    else
                    {
                        mapIsEmpty = true;
                    }
    'else' should be executed but sql ce return NULL and 'if' is execured and 'else' isnt executed.
    Saturday, February 06, 2010 12:51 PM
  • UP.How can i do sql ce dont return NULL if table is empty?
    Saturday, February 06, 2010 3:13 PM
  • How annoying!  The workaround is fine for a simple distinct, but the lack of functionality becomes a real pain with something like "select parent, count(distinct children) child_qty from table group by parent"...
    Tuesday, May 11, 2010 9:22 PM
  • Why am I getting this error with CE 3.5?


    There was an error parsing the query. [ Token line number = 1,Token line offset = 14,Token in error = DISTINCT ]

    CowEventsTableAdapter.Connection.Open()
    cmd = New SqlServerCe.SqlCeCommand("SELECT Count(DISTINCT CowID) FROM CowEvents WHERE FarmID=" & GlobalsClass.intFarmID & " AND EventMonth = " & DatePart(DateInterval.Month, Today) & " And EventDay = " & DatePart(DateInterval.Day, Today) & " And EventYear = " & DatePart(DateInterval.Year, Today) & "AND EventType ='Routine'" & " AND Complete =1", CowEventsTableAdapter.Connection)
    reader = cmd.ExecuteReader()
    While reader.Read()
    intRoutine = reader.GetValue(0)
    End While
    CowEventsTableAdapter.Connection.Close()

    Could you pls give more explanation on your needs? It will be better for answering your question.
    Wednesday, March 16, 2011 10:20 PM