Using COUNT DISTINCT

More
9 years 10 months ago #22 by paulsk
paulsk created the topic: Using COUNT DISTINCT
[PGNP32-1.2.8.1110 with .NET 2.0]

I am using the following SQL:

SELECT COUNT(DISTINCT GroupID) AS count FROM MyTable

but i am getting a System.NullReferenceException being thrown when the SQL is executed. I can't see what could be wrong with the SQL, especially since this same SQL is used successfully for Oracle and SqlServer.

Please Log in or Create an account to join the conversation.

More
9 years 10 months ago #24 by Moderator
Moderator replied the topic: Re: Using COUNT DISTINCT
We were unable to reproduce the bug. We used the following code:
dbcmd = new OleDbCommand("select count(distinct id) from testtbl", dbcon);
Console.WriteLine("Count: {0}", dbcmd.ExecuteScalar());

The returned value is Int64. Could you send the table definition and the fragment of code that executes the query?

Please Log in or Create an account to join the conversation.

More
9 years 10 months ago #26 by paulsk
paulsk replied the topic: Re: Using COUNT DISTINCT
My code snippet is this.
    System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection("db-connection-string")
    System.Data.OleDb.OleDbCommand stmt = con.CreateCommand();
    stmt.CommandText = "SELECT COUNT(DISTINCT GroupID) AS count FROM MyTable";
    System.Data.OleDb.OleDbDataReader rs = stmt.ExecuteReader();

I have also found the source of my problem :D, it is the
AS count
part of the SQL. When this is removed I can use either ExecuteReader() or ExecuteScalar() okay.

It is worth noting that my SQL snippet is used okay for Oracle and Sqlserver with .NET, and also with Oracle, Sqlserver and Postgres with Java. Any thoughts as to why it will not work with your driver?

Please Log in or Create an account to join the conversation.

More
9 years 10 months ago #28 by Moderator
Moderator replied the topic: Re: Using COUNT DISTINCT
There was a bug in <select_element> production rule definition. We have extended the rule to allow COUNT and some other words in the column alias.

We'll publish the fix in the next release of the provider. Please PM or e-mail us if you need the fix immediately.

Thank you for the good catch!

Please Log in or Create an account to join the conversation.

More
9 years 10 months ago #31 by paulsk
paulsk replied the topic: Re: Using COUNT DISTINCT
I have worked around this issue so an immediate fix is not necessary. When will 1.3 be released?

Please Log in or Create an account to join the conversation.

More
9 years 10 months ago #33 by Moderator
Moderator replied the topic: Re: Using COUNT DISTINCT
We are trying to determine the approximate release date. Most likely it will be middle of Summer 2009.

Please Log in or Create an account to join the conversation.

Time to create page: 0.113 seconds
Powered by Kunena Forum