Strange event behavior
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;