Can I use a QueryString parameter within the TOP x line of a named query?
E.g.
SELECT TOP :Samples * FROM table
With :Samples in there, I get a non-descript error:
Without it, it works… Is this a bug?
EDIT: nevermind, should have read the help first!
QueryString params need to use {} instead of :
E.g.
SELECT TOP {Samples} * FROM table
I am having similar issues. I am trying to use the alarm analysis page for Perspective:
All the named queries are written for MySQL and use LIMIT, which is not supported in MSSQL, so I am trying to use SELECT TOP instead. The original code uses a Value parameter “limit”, but when I use that same parameter after SELECT TOP I get all kinds of unhelpful errors.
Changing it from a value parameter to a QueryString parameter works, so thanks for the tip left in your post. But do you have any idea why it won’t work as a Value parameter?
Purely an implementation detail of the JDBC driver.
Thanks for letting me know, it appears there are some other idiosyncracies as well. I was commenting out the existing SQL, and modifying a copy of it further down. But it complained about and “index out of range” which seems to be an error that shows up for a whole host of reasons, so that didn’t narrow it down. With the commented sections deleted I’ve got it working. What a pain!
Yes, that’s an unfortunate bug in named queries that we haven’t yet resolved.
Well, comments are not strictly part of SQL. They are part of a DB platform’s scripting support. JDBC drivers are not required to support that, and it works only by accident for some DBs.
If comments are not part of SQL then I guess the named queries objects in Ignition need to have a feature to allow for documentation. Ignition can strip it before sending it to the JDBC driver.
These are the new queries, which are working for our setup based on an MSSQL database. I hope it helps someone else to get the alarm analysis page going. I provide no guarantees, this is just to get through some of the issues I encountered quicker. You will have to verify for yourself that it does what you need it do and modify as required.
Note: as per earlier in the thread, the parameter “limit” cannot be passed as a value, it needs to be made a “QueryString”.
Also note: one of the queries didn’t have the start and end dates set as “DateTime” data type, can’t remember which it was, but it should be changed.
Alarm History:
SELECT TOP {limit}
FORMAT(e.eventTime, 'DD/MM/YYYY hh:mm:ss') eventTime,
e.displayPath,
LEFT(source,PATINDEX('%/alm:%',source)-2) name,
e.eventType,
CASE WHEN e.priority = 0 THEN 'Diagnostic' WHEN e.priority = 1 THEN 'Low' WHEN e.priority = 2 THEN 'Medium' WHEN e.priority = 3 THEN 'High' WHEN e.priority = 4 THEN 'Critical' ELSE '' END priority,
COALESCE(COALESCE(COALESCE(d.intvalue, d.floatvalue), d.strvalue), '') eventValue,
COALESCE(ack.strvalue, '') ackUser
FROM
alarm_events e
LEFT JOIN alarm_event_data d ON d.id = e.id AND d.propname = 'eventValue'
LEFT JOIN alarm_event_data ack ON ack.id = e.id AND ack.propname = 'ackUser'
WHERE
eventtime BETWEEN :startDate AND :endDate AND priority BETWEEN :minPriority AND :maxPriority AND
(((:active = 1) AND (e.eventtype = 0)) OR ((:clear = 1) AND (e.eventtype = 1)) OR ((:ack = 1 AND e.eventtype = 2)))
ORDER BY
e.eventTime DESC
Alarm Summary:
SELECT
a.priority,
SUM(CASE WHEN c.eventtime IS NULL THEN 1 ELSE 0 END) active,
SUM(CASE WHEN c.eventtime IS NULL THEN 0 ELSE 1 END) cleared,
SUM(CASE WHEN k.eventtime IS NULL THEN 1 ELSE 0 END) unacknowledged,
COUNT(*) total,
AVG(CASE WHEN k.eventtime IS NULL THEN 0 ELSE DATEDIFF(SECOND, a.eventtime, COALESCE(k.eventtime, CURRENT_TIMESTAMP)) END) acknowledgeTime,
AVG(CASE WHEN c.eventtime IS NULL THEN 0 ELSE DATEDIFF(SECOND, a.eventtime, COALESCE(c.eventtime, CURRENT_TIMESTAMP)) END) clearTime,
SUM(DATEDIFF(SECOND, a.eventtime, COALESCE(c.eventtime, CURRENT_TIMESTAMP))) totalTime
FROM
alarm_events a
LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1
LEFT JOIN alarm_events k ON k.eventid = a.eventid AND k.eventtype = 2
WHERE
a.eventtime BETWEEN :startDate AND :endDate AND a.eventtype = 0
GROUP BY
a.priority
ORDER BY
a.priority ASC
Alarm Summary Table:
SELECT
CASE a.priority
WHEN 1 THEN 'Low'
WHEN 2 THEN 'Medium'
WHEN 3 THEN 'High'
WHEN 4 THEN 'Critical'
ELSE 'Diagnostic'
END as 'Priority',
SUM(CASE WHEN c.eventtime IS NULL THEN 1 ELSE 0 END) active,
SUM(CASE WHEN c.eventtime IS NULL THEN 0 ELSE 1 END) cleared,
SUM(CASE WHEN k.eventtime IS NULL THEN 1 ELSE 0 END) unacknowledged,
COUNT(*) total,
AVG(CASE WHEN k.eventtime IS NULL THEN 0 ELSE DATEDIFF(SECOND, a.eventtime, COALESCE(k.eventtime, CURRENT_TIMESTAMP)) END) acknowledgeTime,
AVG(CASE WHEN c.eventtime IS NULL THEN 0 ELSE DATEDIFF(SECOND, a.eventtime, COALESCE(c.eventtime, CURRENT_TIMESTAMP)) END) clearTime,
SUM(DATEDIFF(SECOND, a.eventtime, COALESCE(c.eventtime, CURRENT_TIMESTAMP))) totalTime
FROM
alarm_events a
LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1
LEFT JOIN alarm_events k ON k.eventid = a.eventid AND k.eventtype = 2
WHERE
a.eventtime
BETWEEN :startDate AND :endDate
AND a.eventtype = 0
GROUP BY
a.priority
ORDER BY
a.priority ASC
Alarms By Hour:
SELECT
DATEPART(hour,a.eventtime) label,
COUNT(*) total,
SUM(DATEDIFF(SECOND, a.eventtime, COALESCE(c.eventtime, CURRENT_TIMESTAMP))) duration
FROM
alarm_events a
LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1
WHERE
a.eventtime BETWEEN :startDate AND :endDate AND a.eventtype = 0
GROUP BY
DATEPART(hour,a.eventtime)
ORDER BY
DATEPART(hour,a.eventtime) ASC
Alarms Duration:
SELECT TOP {limit}
CASE WHEN a.displaypath = '' THEN 'Unknown' ELSE a.displaypath END displaypath,
SUM(DATEDIFF(SECOND, a.eventtime, COALESCE(c.eventtime, CURRENT_TIMESTAMP))) duration
FROM
alarm_events a
LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1
WHERE
a.eventtime BETWEEN :startDate AND :endDate AND a.eventtype = 0
GROUP BY
a.displaypath
ORDER BY
duration DESC, a.displaypath ASC
Alarms Frequency:
SELECT TOP {limit}
CASE WHEN a.displaypath = '' THEN 'Unknown' ELSE a.displaypath END displaypath,
COUNT(*) total
FROM
alarm_events a
WHERE
a.eventtime BETWEEN :startDate AND :endDate AND a.eventtype = 0
GROUP BY
a.displaypath
ORDER BY total DESC , a.displaypath ASC
Longest Duration Alarm:
SELECT TOP 1
CASE WHEN a.displaypath = '' THEN 'Unknown' ELSE a.displaypath END displaypath,
SUM(DATEDIFF(SECOND, a.eventtime, COALESCE(c.eventtime, CURRENT_TIMESTAMP))) total
FROM
alarm_events a
LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1
WHERE
a.eventtime BETWEEN :startDate AND :endDate AND a.eventtype = 0
GROUP BY
a.displaypath
ORDER BY total DESC
Most Frequent Alarm:
SELECT TOP 1
CASE WHEN a.displaypath = '' THEN 'Unknown' ELSE a.displaypath END displaypath,
COUNT(*) total
FROM
alarm_events a
WHERE
a.eventtime BETWEEN :startDate AND :endDate AND a.eventtype = 0
GROUP BY
a.displaypath
ORDER BY total DESC
Tip: use the </> code formatting button when pasting code to preserve indentation and apply syntax highlighting. For short bit’s of inline code you can surround with `` backticks to format it like this. There’s an edit button (pencil icon) below your post that enables you to fix it. Thanks.
I remember having issues with MSSQL of the alarm analysis query of the Exchange resource.
Fixed it like this:
DECLARE @limit as int = :limit
SELECT TOP (@limit)
From iuguigyu
Where uyguygu
Thanks Transistor and Deon, I’ll try to remember!
I wish I saw this before doing this all myself. The only other issue I had was with the following LEFT(source,PATINDEX('%/alm:%',source)-2) name
In instanced where PATINDEX doesn’t find /alm:, you will get an invalid length parameter error. Because you’ll get -2 as the length. I solved this by doing this:
LEFT(source,CHARINDEX('/alm:',source+' /alm:')-2) name,
You can do it with PATINDEX as well, I just added the +' /alm:' at the end of source to always guarantee a result of 0.