MySQL Forums
Forum List  »  Events (Job Scheduler)

Strange event behavior
Posted by: Aldo Doronzo
Date: June 11, 2012 03:07AM

Good morning everyone! I'm having a strange behavior for the query you find at the bottom.

As you can see, I use the IF in the select and it properly works. It writes 'No travel' when the date is '1899', otherwise it writes the normal date.

if I execute the INSERT/SELECT statement by myself, it works perfectly. However, if I use the create event (the one at the bottom), the event is properly executed, but the IF part has a strange behavior, and it reports almost always 'No travel'.

This is so strange, please help me!

Lookin' forward


Aldo



CREATE EVENT AggiornamentoFmsOdierno
ON SCHEDULE EVERY 1 DAY STARTS '2012-06-08 03:00:00'
DO
INSERT INTO fms_vehicle_day_sum_fercam (F, Plate, GroupId, Day, `First Start`, `Last Stop`, `Km Start`, `Km Stop`, `Total Cons Start`, `Total Cons Stop`, `Dist Diff`, `Cons Total`, `Cons Drive`, `Cons Stand`)
SELECT
vehicles.Code,
vehicles.Name AS Plate,
vehicles.GroupId AS GroupId,
fms_vehicle_day_sum.Day,
IF(fms_vehicle_day_sum.FirstStart = '1899-12-30 00:00:00','No Travel',fms_vehicle_day_sum.FirstStart) `First Start`,
IF(fms_vehicle_day_sum.LastStop = '1899-12-30 00:00:00','No Travel',fms_vehicle_day_sum.LastStop) `Last Stop`,
fms_vehicle_day_sum.Km_Start AS `Km Start`,
fms_vehicle_day_sum.Km_Stop AS `Km Stop`,
fms_vehicle_day_sum.FMS_TotalConsStart AS `Total Cons Start`,
fms_vehicle_day_sum.FMS_TotalConsStop AS `Total Cons Stop`,
fms_vehicle_day_sum.DistDiff AS `Dist Diff`,
fms_vehicle_day_sum.ConsTotal AS `Cons Total`,
fms_vehicle_day_sum.ConsDrive AS `Cons Drive`,
fms_vehicle_day_sum.ConsStand AS `Cons Stand`
FROM fms_vehicle_day_sum
LEFT JOIN vehicles ON vehicles.id=fms_vehicle_day_sum.VehicleId
WHERE (fms_vehicle_day_sum.Day = SUBDATE(CURDATE(), 1)) AND (vehicles.code LIKE 'F%')
ORDER BY vehicles.Code;

Options: ReplyQuote


Subject
Views
Written By
Posted
Strange event behavior
3044
June 11, 2012 03:07AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.