List:General Discussion« Previous MessageNext Message »
From:Mike Blezien Date:May 17 2006 2:21pm
Subject:Re: Baffled by query error syntax
View as plain text  
Hi Sheeri,

Is your 'u.modified' column a TIME datatype '00:00:00'

Mike
----- Original Message ----- 
From: "sheeri kritzer" <awfief@stripped>
To: "Mike Blezien" <mickalo@stripped>
Cc: "MySQL List" <mysql@stripped>
Sent: Wednesday, May 17, 2006 9:10 AM
Subject: Re: Baffled by query error syntax


Mike,

I can't really help except to ask if you're sure you copied and pasted
the query correctly.  I did a similar query against a test system:

select u.uid,u.username,b.buddyUid,SEC_TO_TIME(SUM(TIME_TO_SEC(u.modified)))
as mins from Users u left join BuddyList b on u.uid = b.uid where
u.modified >= DATE_SUB(NOW(),INTERVAL 14 DAY) and country="au" group
by u.uid having mins >= '2' order by mins;

Similar joins, similar where clause, etc and yet I got an answer
(almost 700 rows, took 2 seconds) while you got a syntax error.
select @@version;
+---------------------+
| @@VERSION           |
+---------------------+
| 4.1.12-standard-log |
+---------------------+

So I'm not sure what to recommend other than trying the query again to
make sure there aren't typos.

MySQL usually gives a syntax error *where* the error happens.  In this
case, it would indicate a problem with "SEC_TO_TIME(" but there
shouldn't be a problem, both according to the manual AND according to
my example.

I would prepare for a bug report -- create 2 new tables in the test
db, in this case you don't need a lot of test data, do the join, and
if you still get the problem, submit a bug report (you've just done
the "steps to recreate" part).  Many times I've done this and realized
where my bug was because the query worked in the test table.

-Sheeri

On 5/17/06, Mike Blezien <mickalo@stripped> wrote:
> Hello,
>
> this is a continued problem we are having from a earlier posting to the list
> regarding a query. We need to calculate the SUM of the column
> 'agent_product_time' which is a TIME datatype column and according to the
> manual:
> http://dev.mysql.com/doc/refman/4.1/en/date-and-time-type-overview.html
> this is the way to SUM the total time, which keeps producing a syntax error 
> and
> figure out why
>
> MySQL version 4.1.12
>
> ---------------------------------------------------------------------------------------------------
> SELECT c.account_id,a.name,a.company,
> SEC_TO_TIME(SUM(TIME_TO_SEC(c.agent_product_time))) AS mins
> FROM account a LEFT JOIN calls c ON c.account_id = a.id
> WHERE c.calldate >= DATE_SUB(NOW(),INTERVAL 14 DAY)
> AND c.agent_id = 2 GROUP BY c.account_id HAVING mins >= '500' ORDER BY mins
>
> ERROR:
> #1064 - 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
> '( SUM( TIME_TO_SEC( c . agent_product_time ) ) ) AS mins  FROM account a LEFT
> JO' at line 1
> --------------------------------------------------------------------------
>
> What would be producing the syntax error here.??
>
> Again, any help would be much appreciated.
>
> Mike(mickalo)Blezien
> ===============================
> Thunder Rain Internet Publishing
> Providing Internet Solution that Work
> http://www.thunder-rain.com
> ===============================
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>

Thread
Baffled by query error syntaxMike Blezien17 May
  • Re: Baffled by query error syntaxsheeri kritzer17 May
    • Re: Baffled by query error syntaxMichael Stassen18 May
  • Re: Baffled by query error syntaxMike Blezien17 May
  • Re: Baffled by query error syntaxMichael Stassen18 May