Defining Functions

[s4dadmin]

I am getting an error in my sql syntax when it comes to using
aggregate functions. In mysql you have to define it. Here is the error
I am getting using the count function.

MySQL][ODBC 3.51 Driver][mysqld-4.0.12-max-debug]You have an error in
your SQL syntax. Check the manual that corresponds to your MySQL
server version for the right syntax to use near 'SELECT Count(Topics.
TopicID) From Topics Where Forums.ForumID =

In the help menu it lists this sql statement as the example.
CREATE [AGGREGATE] FUNCTION function_name RETURNS
{STRING|REAL|INTEGER}
SONAME shared_library_name

Can anyone give me a real example of how they wrote their function?
Where do you enter this information?
Once the function is installed how do I use it in my sql statement?

 

[swampBoogie]

You don't need to create the count function.

If you get an error it is always helpful to post the code that caused the error.

 

[s4dadmin]

Ok, I see the functions in mysql are something different.

Here is the code. It works perfectly in MS Access but I am having problems with mysql.

SQL = "SELECT Category.CategoryName, Forums.ForumID," _
& " Forums.ForumName, Forums.ForumDescription," _
& " (SELECT Count(Topics.TopicID) From Topics" _
& " Where Forums.ForumID = Topics.ForumID) As lngtopicount," _
& " (SELECT Count(Posts.PostID) From Posts INNER JOIN Topics" _
& " ON Posts.TopicID = Topics.TopicID" _
& " WHERE Forums.ForumID = Topics.ForumID) As lngpostcount," _
& " (SELECT TOP 1 users.username From users INNER JOIN (Posts" _
& " INNER JOIN Topics ON (Posts.TopicID = Topics.TopicID))" _
& " ON users.userID = Posts.MemberID" _
& " WHERE Forums.ForumID = Topics.ForumID" _
& " Order by Posts.PostDate DESC) As lastpostmember," _
& " (SELECT LAST (Posts.PostDate) From Posts INNER JOIN Topics" _
& " ON Posts.TopicID = Topics.TopicID" _
& " WHERE Forums.ForumID = Topics.ForumID) As lastpostdate" _
& " FROM Category INNER JOIN Forums" _
& " ON Category.CategoryID = Forums.CategoryID" _
& " ORDER BY Category.CategoryOrder, [Forums.ForumOrder"

 

[swampBoogie]

Mysql does not scalar subqueries until version 4.1

First and last are (non-relational) functions supported by Access only. In most cases max and min can be used.

TOP 1 can be replaced by max/min

You have to rewrite this with group by.

 

[s4dadmin]

Thanks swampBoogie, I see that all I need to do is to make small changes to my sql statement.