MySQL Error

[shubham_chakraborty@…]

Report execution failed: (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 'DECIMAL) END as Estimated_work,\r\n CASE WHEN totalhours.value = OR totalhou' at line 10")

what is the query syntax error i need to change to make the timing and estimation plugin work in sync with Mysql database..?

Here is the query for Ticket Hours report????

SELECT color, style, ticket, summary, component ,version, severity,

milestone, status, owner, Estimated_work, Total_work, billable,_ord

FROM (

SELECT p.value AS color,

as style, t.id AS ticket, summary AS summary, -- ## Break line here component,version, severity, milestone, status, owner, CASE WHEN EstimatedHours.value = OR EstimatedHours.value IS NULL THEN 0

ELSE CAST( EstimatedHours.value AS DECIMAL) END as Estimated_work,

CASE WHEN totalhours.value = OR totalhours.value IS NULL THEN 0

ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work,

CASE WHEN billable.value = '1' THEN 'Y' else 'N' END as billable, time AS created, changetime AS modified, -- ## Dates are formatted description AS _description_, -- ## Uses a full row changetime AS _changetime, reporter AS _reporter ,0 as _ord

FROM ticket as t JOIN enum as p ON p.name=t.priority AND p.type='priority'

LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'

AND EstimatedHours.Ticket = t.Id

LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'

AND totalhours.Ticket = t.Id

LEFT JOIN ticket_custom as billable ON billable.name='billable'

AND billable.Ticket = t.Id

WHERE t.status IN ($NEEDINFO, $REOPENED, $ASSIGNED, $CLOSED, $NEW, $ACCEPTED, $PENDING)

AND billable.value in ($BILLABLE, $UNBILLABLE)

UNION

SELECT '1' AS color,

'background-color:#DFE;' as style, 0 as ticket, 'Total' AS summary, NULL as component,NULL as version, NULL as severity, NULL as milestone, 'Time Remaining: ' as status, CAST(

SUM(CASE WHEN EstimatedHours.value = OR EstimatedHours.value IS NULL THEN 0

ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -

SUM(CASE WHEN totalhours.value = OR totalhours.value IS NULL THEN 0

ELSE CAST( totalhours.value AS DECIMAL ) END) AS CHAR(512)) as owner, SUM(CASE WHEN EstimatedHours.value = OR EstimatedHours.value IS NULL THEN 0

ELSE CAST( EstimatedHours.value AS DECIMAL ) END) as Estimated_work,

SUM(CASE WHEN totalhours.value = OR totalhours.value IS NULL THEN 0

ELSE CAST( totalhours.value AS DECIMAL ) END) as Total_work,

NULL as billable, NULL as created, NULL as modified, -- ## Dates are formatted

NULL AS _description_, NULL AS _changetime, NULL AS _reporter ,1 as _ord

FROM ticket as t JOIN enum as p ON p.name=t.priority AND p.type='priority'

LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'

AND EstimatedHours.Ticket = t.Id

LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'

AND totalhours.Ticket = t.Id

LEFT JOIN ticket_custom as billable ON billable.name='billable'

AND billable.Ticket = t.Id

WHERE t.status IN ($NEEDINFO, $REOPENED, $ASSIGNED, $CLOSED, $NEW, $ACCEPTED, $PENDING)

AND billable.value in ($BILLABLE, $UNBILLABLE)

) as tbl ORDER BY _ord ASC, ticket

[Russ Tyndall]

These reports have worked with mysql in the past, so I am going to need more information to help you out.

• What version of mysql are you using?
• What version of Timing and Estimation plugin are you using.
• Please paste your system info (this is on the about page when logged in as trac-admin)
  • This will include many relevant version numbers

Thanks for your help

Russ

[anonymous]

System Information Trac: 0.11 Python: 2.5.4 (r254:67916, Dec 23 2008, 15:10:54) [MSC v.1310 32 bit (Intel)] setuptools: 0.6c9 MySQL: server: "4.1.22-community-nt", client: "5.0.27", thread-safe: 1 MySQLdb: 1.2.2 Genshi: 0.5.1 mod_python: 3.3.1 CustomFieldAdmin: 0.2.2 jQuery: 1.2.3

Do you need the INI file as well....The above error was removed in the normal VIEW TICKETS when i changed the 'int' type to 'unsigned' Does DECIMAL here create the same problem???? I had replaced 'DECIMAL' with 'unsigned'to check if that is the cause of the problem, and that seemed to have removed the error message, although I am not getting results after that...maybe because the type never matches..or IS IT SOMETHING ELSE????

[shubham_chakraborty@…]

​http://trac-hacks.org/browser/timingandestimationplugin/branches/trac0.11-Permissions

This is the version of Timinig anf Estimation Plugin I am using.. The error and Report SQL query code is in the description above..

sry, i didnt put my name in the prev post.

[shubham_chakraborty@…]

I found a new error, replacing DECIMAL with unsigned makes all the calculations to 0.0 in the hours column. Please provide a solution as tracking effort is very important in our project.

[shubham_chakraborty@…]

TnE 0.0 with 'unsigned' instead of 'decimal' in the report

[Russ Tyndall]

Based on ​the documentation, DECIMAL and INT are both valid MySQL4 datatypes. As such I am not sure why you are experiencing this problem. Perhaps you need to enable the datatypes somehow? (I am not really a mysql user so I cant tell you the answer here). Also please save backups of your reports once you are done editing them (they can be overridden in an upgrade).

Changing DECIMAL to INT will definitely cause rounding errors everywhere (such as all partial hours to 0). However, you might be able to change DECIMAL to REAL or DOUBLE PRECISION and get reasonable results (though if you dont have the decimal type, I am not sure you will have those either).

[Russ Tyndall]

Replying to bobbysmith007:

Also please save backups of your reports once you are done editing them (they can be overridden in an upgrade).

You can also prevent this overwrite by editing the table custom_report in the database and set the version really high, or change the UUID to something else (which will allow it to reinstall the original reports while still having your reports in there).

Also since there have been no other reports, I am going to assume this is solved. Please reopen if you continue to have this problem

HTH, Russ