Hello everyone, this is my first post. I am kind of new using Ignition and I am facing an issue with a Named Query that works when I do testing, but results in an "Error_Exception" when Binding. Following the Named Query, and some screenshots of what I see:
Query:
-- Parameter definition
DECLARE @StartTime NVARCHAR(MAX) = :StartTime;
DECLARE @EndTime NVARCHAR(MAX) = :EndTime;
DECLARE @TableName NVARCHAR(MAX) = {TableName};
DECLARE @columnName NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);
-- Get column names for Fault Types
DECLARE faultCursor CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND COLUMN_NAME LIKE '%Fault';
-- Build dynamic Fault Types query
SET @sql = '
SELECT
ft1.t_stamp AS Trigger_Time,
ft2.t_stamp AS Clear_Time,
CASE
';
-- Append CASE for each fault type column
OPEN faultCursor;
FETCH NEXT FROM faultCursor INTO @columnName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ' WHEN ft1.' + QUOTENAME(@columnName) + ' = 1 THEN ''' + REPLACE(@columnName, '_Fault', '') + ''' ' + CHAR(10);
FETCH NEXT FROM faultCursor INTO @columnName;
END
CLOSE faultCursor;
DEALLOCATE faultCursor;
SET @sql = @sql + '
ELSE ''Station''
END AS Fault_Type,
DATEDIFF (SECOND, ft1.t_stamp, ft2.t_stamp) AS TTR
FROM
' + @TableName + ' ft2
INNER JOIN
' + @TableName + ' ft1 ON ft1.t_stamp = (
SELECT MAX (t_stamp)
FROM ' + @TableName + '
WHERE Fault_Time_Enable = 1
AND t_stamp < ft2.t_stamp)
WHERE
ft2.Fault_Time_Enable = 0
AND
ft1.t_stamp BETWEEN @StartTime AND @EndTime
AND
ft2.t_stamp BETWEEN @StartTime AND @EndTime;
';
--Execute
EXEC sp_executesql @sql, N'@StartTime DATETIME, @EndTime DATETIME', @StartTime, @EndTime;
This is what I get when Testing:
However, this is what I get when Binding:
Any help would be appreciated.