注册

达梦如何创建函数

blueberry 2024/03/22 773 3 已解决

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:DM8
【操作系统】:
【CPU】:
【问题描述】*:我有一个函数,在MySQL执行的时候可以执行;到DM这边就报DEFINER语法错误;以下是函数,有知道什么原因的么:
CREATE DEFINER=SysDEm@% PROCEDURE getAlarmResultStats()
begin
REPLACE into TestModel_algo_result_device(ipcId,name,status,installType,date,identify,org,noRepeatCnt,repeatTrueCnt,repeatFalseCnt,totalCnt)
SELECT id,name,status,installType,DATE_FORMAT(NOW(),'%Y-%m-%d') date,identify,org,SUM(noRepeatCnt)noRepeatCnt,SUM(repeatTrueCnt)repeatTrueCnt,SUM(repeatFalseCnt)repeatFalseCnt,SUM(noRepeatCnt+repeatTrueCnt+repeatFalseCnt) as totalCnt FROM (
select id,name,status,installType,DATE_FORMAT(NOW(),'%Y-%m-%d') date,identify,org,IF(noRepeatCnt>0,noRepeatCnt,0) noRepeatCnt,0 as repeatTrueCnt,0 as repeatFalseCnt from tb_ipcDevice ipc left join (
select cam_id,count(1) as noRepeatCnt from tb_algoHistory ah where ah.alarm_time>= DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') and ah.alarm_time < DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') group by ah.cam_id) b on ipc.identify = b.cam_id
UNION ALL
select id,name,status,installType,DATE_FORMAT(NOW(),'%Y-%m-%d') date,identify,org,0 as noRepeatCnt,IF(repeatCnt>0,repeatCnt,0) repeatTrueCnt,0 as repeatFalseCnt from tb_ipcDevice ipc left join ( select cam_id,count(1) as repeatCnt from tb_algoRepeat ah where ah.alarm_time>= DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') and ah.alarm_time < DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') group by ah.cam_id) b on ipc.identify = b.cam_id
UNION ALL
select id,name,status,installType,DATE_FORMAT(NOW(),'%Y-%m-%d') date,identify,org,0 as noRepeatCnt,0 as repeatTrueCnt ,IF(repeatFalseCnt>0,repeatFalseCnt,0) repeatFalseCnt from tb_ipcDevice ipc left join (
select cam_id,count(1) as repeatFalseCnt from tb_algoRepeatFalse ah where ah.alarm_time>= DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') and ah.alarm_time < DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') group by ah.cam_id) b on ipc.identify = b.cam_id
) result GROUP BY date,identify;

end

回答 0
暂无回答
扫一扫
联系客服