sql - 如何在使用聚合函数时修复 SQL 错误 SAP HANA
问题描述
如果我在 SAP HANA 中运行此 sql,它会生成以下错误。
(SQL 编辑器)无法执行 'WITH l1 AS( SELECT REMOTE_SOURCE_NAME,OWNER,NAME,MAX(START_TIME) START_TIME,RESULT_STATE FROM ...' 错误:(dberror) 266 - 不一致的数据类型:只有数字类型可用于 SUM/AVG /STDDEV/VAR 函数:第 43 行第 7 列(位置 654)
如果我排除这部分 sql select sum(LOADING_TIME) AS TOTAL_LOADING_TIME
,那么它可以工作。
我该如何解决问题total_loading_time
?
WITH l1 AS(
SELECT REMOTE_SOURCE_NAME,OWNER,NAME,MAX(START_TIME) START_TIME,RESULT_STATE
FROM "MEAG_EIM_SHARED"."meag.eim.shared::replication.Log"
WHERE TYPE = 'INITIAL' AND RESULT_STATE = 'COMPLETED'
AND REMOTE_SOURCE_NAME='RS_SDI_IMMO'
group by REMOTE_SOURCE_NAME,OWNER,NAME,RESULT_STATE
order by NAME ASC),
l2 AS
(SELECT REMOTE_SOURCE_NAME,OWNER,NAME,START_TIME,END_TIME,RESULT_STATE
FROM "MEAG_EIM_SHARED"."meag.eim.shared::replication.Log"
WHERE TYPE = 'INITIAL' AND RESULT_STATE = 'COMPLETED'
AND REMOTE_SOURCE_NAME='RS_SDI_IMMO'
group by REMOTE_SOURCE_NAME,OWNER,NAME,RESULT_STATE,START_TIME,END_TIME
order by NAME ASC)
select sum(LOADING_TIME) AS TOTAL_LOADING_TIME
from(
select layer1.REMOTE_SOURCE_NAME,layer1.OWNER,layer1.name,layer1.RESULT_STATE,layer1.start_time start_time,layer2.end_time end_time,
SECONDS_BETWEEN(layer1.START_TIME,layer2.END_TIME) || 's' LOADING_TIME
from l1 layer1 inner join l2 AS layer2
on layer1.start_time=layer2.start_time
order by name);
解决方案
LOADING_TIME
将字符“s”连接到它 - 将数值转换为字符串。对于字符串,没有SUM()
功能。
如果“s”很重要,那么在求和之后添加它可以解决问题。否则,将单位面额(我猜 s 代表秒)添加到列名将允许客户端工具仍然使用数值(即排序时)