Добрый день.
Столкнулся с интересной проблемой.
Запрос, нормально работающий при вызове из консоли DBever, не хочет сохраняться в процедуре.
select (CAST(h.`time`/60 AS int) * 60) as tm, h.dev_id metric, count(*) value
FROM devices d
inner join orgs o on d.org_id = o.id
left outer JOIN history h on h.dev_id =d.id
where (o.parent_org = :org_uid or o.id = :org_uid )
and (h.`time` > :_from_time AND h.`time` < :_to_time)
GROUP by h.dev_id,cast((h.time/60) as int)
ORDER BY h.time/60
соответственно в процедуре
CREATE DEFINER=`root`@`%` PROCEDURE `timeline_device_online`(org_uid char(36), _from_time bigint, _to_time bigint )
BEGIN
select (CAST(h.`time`/60 AS int) * 60) as tm, h.dev_id metric, count(*) value
FROM devices d
inner join orgs o on d.org_id = o.id
left outer JOIN history h on h.dev_id =d.id
where (o.parent_org = org_uid or o.id = org_uid )
and (h.`time` > _from_time AND h.`time` < _to_time)
GROUP by h.dev_id,cast((h.time/60) as int)
ORDER BY h.time/60;
END
При сохранении возвращает ошибку
SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'int)
ORDER by h.`time`/60;
END' at line 10
Есть идеи?
Отредактированно awarm (15.04.2022 18:32:00)
