# Named queries with QueryString in SELECT TOP x

**URL:** <https://forum.inductiveautomation.com/t/named-queries-with-querystring-in-select-top-x/33109>\
**Category:** Ignition\
**Created:** [March 16, 2020, 6:33am UTC](https://forum.inductiveautomation.com/t/named-queries-with-querystring-in-select-top-x/33109 "2020-03-16T06:33:42Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [March 16, 2020, 6:33am UTC](https://forum.inductiveautomation.com/t/named-queries-with-querystring-in-select-top-x/33109/1 "2020-03-16T06:33:42Z")

</div>

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:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/2/21cb4e549472faa49ed32c878e43fdca1172db9d.png)

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/d/d85bd1f4a692de57705c7de06e4d72a9f824672b.png)

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`

---

<div class="post-metadata">

**Author:** ![Bart.van.Deurzen](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@Bart.van.Deurzen](https://forum.inductiveautomation.com/u/Bart.van.Deurzen)\
**Post date:** [February 26, 2022, 1:56am UTC](https://forum.inductiveautomation.com/t/named-queries-with-querystring-in-select-top-x/33109/2 "2022-02-26T01:56:19Z")

</div>

I am having similar issues. I am trying to use the alarm analysis page for Perspective:

> **[Ignition Exchange](https://inductiveautomation.com/exchange/57/overview)**
>
> Discover, share, and download community made Ignition resources in collaboration with your organization or the Ignition community.

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?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 26, 2022, 1:59am UTC](https://forum.inductiveautomation.com/t/named-queries-with-querystring-in-select-top-x/33109/3 "2022-02-26T01:59:32Z")

</div>

> [@Bart.van.Deurzen](#):
>
> But do you have any idea why it won’t work as a Value parameter?

Purely an implementation detail of the JDBC driver.

---

<div class="post-metadata">

**Author:** ![Bart.van.Deurzen](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@Bart.van.Deurzen](https://forum.inductiveautomation.com/u/Bart.van.Deurzen)\
**Post date:** [February 27, 2022, 12:32am UTC](https://forum.inductiveautomation.com/t/named-queries-with-querystring-in-select-top-x/33109/4 "2022-02-27T00:32:01Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [February 28, 2022, 5:14pm UTC](https://forum.inductiveautomation.com/t/named-queries-with-querystring-in-select-top-x/33109/5 "2022-02-28T17:14:55Z")

</div>

Yes, that’s an unfortunate bug in named queries that we haven’t yet resolved.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 28, 2022, 5:17pm UTC](https://forum.inductiveautomation.com/t/named-queries-with-querystring-in-select-top-x/33109/6 "2022-02-28T17:17:27Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Bart.van.Deurzen](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@Bart.van.Deurzen](https://forum.inductiveautomation.com/u/Bart.van.Deurzen)\
**Post date:** [March 2, 2022, 3:03am UTC](https://forum.inductiveautomation.com/t/named-queries-with-querystring-in-select-top-x/33109/7 "2022-03-02T03:03:40Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Bart.van.Deurzen](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@Bart.van.Deurzen](https://forum.inductiveautomation.com/u/Bart.van.Deurzen)\
**Post date:** [March 2, 2022, 3:12am UTC](https://forum.inductiveautomation.com/t/named-queries-with-querystring-in-select-top-x/33109/8 "2022-03-02T03:12:18Z")

</div>

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:**

```auto
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:**

```auto
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:**

```auto
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:**

```auto
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:**

```auto
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:**

```auto
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:**

```auto
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:**

```auto
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

```

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [March 2, 2022, 9:23am UTC](https://forum.inductiveautomation.com/t/named-queries-with-querystring-in-select-top-x/33109/9 "2022-03-02T09:23:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![deon.korb](https://avatars.discourse-cdn.com/v4/letter/d/8c91f0/32.png) [@deon.korb](https://forum.inductiveautomation.com/u/deon.korb)\
**Post date:** [March 2, 2022, 9:37am UTC](https://forum.inductiveautomation.com/t/named-queries-with-querystring-in-select-top-x/33109/10 "2022-03-02T09:37:43Z")

</div>

I remember having issues with MSSQL of the alarm analysis query of the Exchange resource.

Fixed it like this:

```auto
DECLARE @limit as int = :limit

SELECT TOP (@limit)
From iuguigyu
Where uyguygu

```

---

<div class="post-metadata">

**Author:** ![Bart.van.Deurzen](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@Bart.van.Deurzen](https://forum.inductiveautomation.com/u/Bart.van.Deurzen)\
**Post date:** [March 3, 2022, 10:42pm UTC](https://forum.inductiveautomation.com/t/named-queries-with-querystring-in-select-top-x/33109/11 "2022-03-03T22:42:10Z")

</div>

Thanks Transistor and Deon, I’ll try to remember!

---

<div class="post-metadata">

**Author:** ![ziad](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ziad/32/6841_2.png) [@ziad](https://forum.inductiveautomation.com/u/ziad)\
**Post date:** [May 5, 2022, 11:14pm UTC](https://forum.inductiveautomation.com/t/named-queries-with-querystring-in-select-top-x/33109/12 "2022-05-05T23:14:30Z")

</div>

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.