How to fix SQL error SAP HANA while using aggregate function

Question

If I run this sql in SAP HANA, It generates the following error.

(SQL Editor) Could not execute 'WITH l1 AS( SELECT REMOTE_SOURCE_NAME,OWNER,NAME,MAX(START_TIME) START_TIME,RESULT_STATE FROM ...' Error: (dberror) 266 - inconsistent datatype: only numeric type is available for SUM/AVG/STDDEV/VAR function: line 43 col 7 (at pos 654)

If I exclude this part of sql select sum(LOADING_TIME) AS TOTAL_LOADING_TIME, then it works.

How can I solve the issue to get 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);

Lars Br. · Accepted Answer · 2020-10-08 10:57:19Z

1

LOADING_TIME has the character „s“ concatenated to it - that turns the numeric value into a string. And for strings there is no SUM() function.

If having the „s“ is important then adding it after the summation will fix the issue. Otherwise, adding the unit denomination (I guess the s stands for seconds) to the column name would allow client tools to still work with the numeric value (I.e. when sorting)

answered Oct 8, 2020 at 10:57

Lars Br.

10.5k2 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to fix SQL error SAP HANA while using aggregate function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related