Skip to main content
InSource KnowledgeCenter

032985 - aggregate value wide format query not displaying correctly

Problem

aggregate value query in the wide format will not display values correctly, they are all the same.

Product Version: 9.1

OS: Win XP

OS_SP: SP2

Solution

found that it was an issue with the OPENQUERY that the wide format uses. see example below: SET QUOTED_IDENTIFIER OFF SELECT * FROM OPENQUERY(INSQL, "SELECT 'SUM SysTimeHour' = Sum([SysTimeHour]), 'SUM SysTimeMin' = Sum([SysTimeMin]), 'SUM SysTimeSec' = Sum([SysTimeSec]) FROM AnalogWideHistory WHERE wwVersion = 'Latest' AND wwRetrievalMode = 'Cyclic' AND wwRowCount = 100 AND DateTime >= DateAdd(wk,-1,GetDate()) AND DateTime <= GetDate()") We modified the query to run as the following example, pulling the SUM function out of the OPENQUERY. see example below: SET QUOTED_IDENTIFIER OFF SELECT 'SUM SysTimeHour' = Sum([SysTimeHour]), 'SUM SysTimeMin' = Sum([SysTimeMin]), 'SUM SysTimeSec' = Sum([SysTimeSec]) FROM OPENQUERY(INSQL, "SELECT [SysTimeHour], [SysTimeMin], [SysTimeSec] FROM AnalogWideHistory WHERE wwVersion = 'Latest' AND wwRetrievalMode = 'Cyclic' AND wwRowCount = 100 AND DateTime >= DateAdd(wk,-1,GetDate()) AND DateTime <= GetDate()") After using this script, the result set would come back as expected. OK to close.

Close Date: 2010-03-31 15:07:39