# Upgraded from 8.1.17 to 8.1.26, now Named Query issue - cannot set parameter at position 1 (values was -1)

**URL:** <https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569>\
**Category:** Ignition\
**Tags:** vision, ignition81\
**Created:** [April 18, 2023, 12:32pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569 "2023-04-18T12:32:55Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [April 18, 2023, 12:32pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569/1 "2023-04-18T12:32:56Z")

</div>

Was trying to upgrade my gateway from 8.1.17 to 8.1.26 but it caused many of my `SELECT` Named Queries to break with the above error. Here's a singular example stack trace

```auto
WARN Vision.Binding.NamedQueryAdapter - Error running query:
com.inductiveautomation.factorypmi.application.gateway.NQuery@5be5d6e8@0ms
On: Invoices.Root Container.Table_Invoices.data
java.lang.Exception: Error running query:
com.inductiveautomation.factorypmi.application.gateway.NQuery@5be5d6e8@0ms
On: Invoices.Root Container.Table_Invoices.data
	at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:311)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: java.sql.SQLException: Could not set parameter at position 1 (values was -1)
Query - conn:196(M) - "SELECT 
0 as 'Check',
`view_listInvoices`.`idx`,
`view_listInvoices`.`dateEntered`,
`view_listInvoices`.`flag`,
`view_listInvoices`.`autoFlag`,
`listCustomers`.`name` as 'Customer',
`listLocations`.`name` as 'Location',
`listAreas`.`name` as 'Area',
`listAssets`.`name` as 'Asset',
`listsalesorders`.`number` as 'soNumber',
`listsalesorders`.`name` as 'soName',
`view_listInvoices`.`number` as 'invNumber',
`view_listInvoices`.`name`,
CONCAT(`listUsers`.`firstName`,' ', `listUsers`.`lastName`) as 'Cust Contact',
(SELECT `name` FROM `listposoinvstatus` WHERE `idx` = `view_listInvoices`.`status`) as 'status',
#`view_listInvoices`.`parentProjectID`,
`view_listInvoices`.`individualId`,
(SELECT
	CASE
	WHEN
		`listsalesorders`.`state` IS NULL
	THEN
		"
	ELSE
		(SELECT description FROM descsostate WHERE descsostate.idx=listsalesorders.state)
	END) as 'SO State',
(SELECT 
		CASE 
		WHEN 
			(`view_listInvoices`.`state` IS NULL OR `view_listInvoices`.`state` IN (4, 5, 6, 7))
		THEN 
	        "
		ELSE
			(SELECT descriptio..."
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:351)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:325)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:278)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:954)
	at com.inductiveautomation.factorypmi.application.db.namedqueries.ClientNamedQueryManager.execute(ClientNamedQueryManager.java:32)
	at com.inductiveautomation.factorypmi.application.gateway.NQuery.execute(NQuery.java:70)
	at com.inductiveautomation.factorypmi.application.gateway.QueryManager$Query.execute(QueryManager.java:668)
	at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:308)
	... 1 common frames omitted
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayResponse$GatewayThrowable: java.sql.SQLException: Could not set parameter at position 1 (values was -1)
Query - conn:196(M) - "SELECT 
0 as 'Check',
`view_listInvoices`.`idx`,
`view_listInvoices`.`dateEntered`,
`view_listInvoices`.`flag`,
`view_listInvoices`.`autoFlag`,
`listCustomers`.`name` as 'Customer',
`listLocations`.`name` as 'Location',
`listAreas`.`name` as 'Area',
`listAssets`.`name` as 'Asset',
`listsalesorders`.`number` as 'soNumber',
`listsalesorders`.`name` as 'soName',
`view_listInvoices`.`number` as 'invNumber',
`view_listInvoices`.`name`,
CONCAT(`listUsers`.`firstName`,' ', `listUsers`.`lastName`) as 'Cust Contact',
(SELECT `name` FROM `listposoinvstatus` WHERE `idx` = `view_listInvoices`.`status`) as 'status',
#`view_listInvoices`.`parentProjectID`,
`view_listInvoices`.`individualId`,
(SELECT
	CASE
	WHEN
		`listsalesorders`.`state` IS NULL
	THEN
		"
	ELSE
		(SELECT description FROM descsostate WHERE descsostate.idx=listsalesorders.state)
	END) as 'SO State',
(SELECT 
		CASE 
		WHEN 
			(`view_listInvoices`.`state` IS NULL OR `view_listInvoices`.`state` IN (4, 5, 6, 7))
		THEN 
	        "
		ELSE
			(SELECT descriptio..."
	at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:407)
	at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:173)
	at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:92)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.NamedQueryFunctions.execute(NamedQueryFunctions.java:49)
	at jdk.internal.reflect.GeneratedMethodAccessor71.invoke(null)
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(null)
	at java.lang.reflect.Method.invoke(null)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.AbstractGatewayFunction.invoke(AbstractGatewayFunction.java:228)
	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:434)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:523)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:590)
	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)
	at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1410)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764)
	at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1665)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:527)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:578)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1570)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1383)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1543)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1305)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:301)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:51)
	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:141)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at org.eclipse.jetty.server.Server.handle(Server.java:563)
	at org.eclipse.jetty.server.HttpChannel.lambda$handle$0(HttpChannel.java:505)
	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:762)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:497)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:282)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)
	at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.runTask(AdaptiveExecutionStrategy.java:416)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.consumeTask(AdaptiveExecutionStrategy.java:385)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.tryProduce(AdaptiveExecutionStrategy.java:272)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.lambda$new$0(AdaptiveExecutionStrategy.java:140)
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:411)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:934)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1078)
	at java.lang.Thread.run(null)

```

And the named query definition - all parameters are used in the `WHERE` clause for filtering -

```auto
SELECT 
0 as 'Check',
`listSalesOrders`.`idx`,
`listSalesOrders`.`dateEntered`,
`listCustomers`.`name` as 'Customer',
`listLocations`.`name` as 'Location',
`listAreas`.`name` as 'Area',
`listAssets`.`name` as 'Asset',
`listSalesOrders`.`number` as 'soNumber',
`listSalesOrders`.`name` as 'soName',

IFNULL(`view_listinvoices`.`number`,'') as 'invNumber',
IFNULL(`view_listinvoices`.`name`,'') as 'invName',

CONCAT(`cu`.`firstName`,' ', `cu`.`lastName`) as 'Cust Contact',
(SELECT `name` FROM `listposoinvstatus` WHERE `idx` = `listSalesOrders`.`status`) as 'status',

(SELECT label FROM descsotype WHERE value = `listSalesOrders`.`type`) as 'type',
`listSalesOrders`.`billThisMonth` as 'billThisMonth',
`listSalesOrders`.`parentProjectID`,
`listSalesOrders`.`individualId`,
`listSalesOrders`.`taxable`,
`listSalesOrders`.`totalPreTaxAmt`,
`listSalesOrders`.`totalTax`,
`view_salesordersalreadybilled`.`totalamountleft` as 'Amount Left',
`listSalesOrders`.`totalMargin`,
`listSalesOrders`.`totalWeeks`,
`listSalesOrders`.`leftMargin`,
`listSalesOrders`.`leftWeeks`,
(Case (`listSalesOrders`.`leftWeeks`) WHEN 0 THEN 0 ELSE (`listSalesOrders`.`leftMargin`)/(`listSalesOrders`.`leftWeeks`) END ) as 'Margin per Week',
`listProjects`.`idx` as 'parentProjectId',
`listProjects`.`number` as 'ProjectNumber',
`listAssets`.`idx` as 'parentAssetId',
`listAreas`.`idx` as 'parentAreaId',
`listLocations`.`idx` as 'parentLocationId',
`listCustomers`.`idx` as 'parentCustomerId',
`view_salesorderscostallocated`.`costAllocatedTotal`,
`view_salesordersalreadybilled`.`alreadyBilledTotal`,
`listSalesOrders`.`totalSOAmount` as 'Total',
`listSalesOrders`.`comment` as 'comment',
(SELECT
	CASE
	WHEN
		`listsalesorders`.`state` IS NULL
	THEN
		"---"
	ELSE
		(SELECT description FROM descsostate WHERE descsostate.idx=listsalesorders.state)
	END) as 'SO State',
(SELECT 
	CASE 
	WHEN 
		(`view_listInvoices`.`state` IS NULL OR `view_listInvoices`.`state` IN (0, 4, 5, 6, 7))
	THEN 
        "---"
	ELSE
		(SELECT description FROM descinvoicestate WHERE descinvoicestate.idx=view_listInvoices.state)
	END) as 'Invoice State',
(SELECT CASE
	WHEN
		`view_listInvoices`.`contNN` = 1
	THEN
		"Not Needed"
	WHEN
		`view_listInvoices`.`sentToContact` = 1
	THEN
		"Sent"
	WHEN 
		`view_listInvoices`.`state` IS NULL AND `view_listInvoices`.`status` = 7
	THEN
		"---"
	ELSE
		"No"
	END) as 'Contact',
(SELECT CASE
	WHEN
		`view_listInvoices`.`acctNN` = 1
	THEN
		"Not Needed"
	WHEN 
		`view_listInvoices`.`receivedConfirmation`=1
	THEN
		"Confirmed"
	WHEN
		`view_listInvoices`.`sentToAccounting` = 1
	THEN
		"Sent"	
	WHEN 
		`view_listInvoices`.`state` IS NULL AND `view_listInvoices`.`status` = 7
	THEN
		"---"
	ELSE
		"No"
	END) as 'Acct',
(SELECT CASE
	WHEN
		`listsalesorders`.`quoteSent`=1
	THEN
		"Sent"
	WHEN
		`listsalesorders`.`quoteNeeded`=1
	THEN
		"Needed"
	ELSE
		"---"
	END) as 'Quote',
(SELECT CASE
	WHEN
		`listsalesorders`.`poSent`=1
	THEN
		"Received"
	WHEN
		`listsalesorders`.`poRequested`
	THEN
		"Requested"
	WHEN
		`listsalesorders`.`poNeeded`=1
	THEN
		"Needed"
	ELSE
		"---"
	END) as 'PO',
(SELECT CASE
	WHEN 
		datediff(now(), view_listInvoices.dateDue) <= 30 AND `view_listInvoices`.`status`!=7
	THEN
		"Out for Payment"
	WHEN
		datediff(now(), view_listInvoices.dateDue) > 30 AND datediff(now(), view_listInvoices.dateDue)<=60 AND `view_listInvoices`.`status`!=7
	THEN
		"Past Due"
	WHEN
		(datediff(now(), `view_listInvoices`.`dateDue`) > 60 AND `view_listInvoices`.`status`!=7)
	THEN
		"Way Past Due"
	WHEN
		(`view_listInvoices`.`status`=7)
	THEN
		"Paid"
	ELSE
		"---"
	END) as 'Payment'

FROM `listSalesOrders`
LEFT JOIN `view_listinvoices` ON `listsalesorders`.`idx` = `view_listinvoices`.`parentSOId`
LEFT JOIN `listUsers` as `soprep` ON `soprep`.`idx` = `listsalesorders`.`individualId`
LEFT JOIN `listUsers` as `ilu` ON `ilu`.`idx` = `view_listinvoices`.`individualId`
LEFT JOIN `listProjects` ON `listProjects`.`idx` = `listSalesOrders`.`parentProjectId`
LEFT JOIN `listAssets` ON `listAssets`.`idx` = `listProjects`.`parentAssetId`
LEFT JOIN `listAreas` ON `listAreas`.`idx` = `listAssets`.`parentAreaId`
LEFT JOIN `listLocations` ON `listLocations`.`idx` = `listAreas`.`parentLocationId`
LEFT JOIN `listCustomers` ON `listCustomers`.`idx` = `listLocations`.`parentCustomerId`
LEFT JOIN `listUsers` as `cu` ON `cu`.`idx` = `listSalesOrders`.`custContactId`
LEFT JOIN `view_salesordersalreadybilled` ON `view_salesordersalreadybilled`.`idx` = `listSalesOrders`.`idx`
LEFT JOIN `view_salesorderscostallocated` ON `view_salesorderscostallocated`.`idx` = `listSalesOrders`.`idx`
WHERE 
(:salesOrderId=-1 OR `listSalesOrders`.`idx`= :salesOrderId) AND
(:customerId=-1 OR `listCustomers`.`idx`=:customerId) AND 
(:locationId=-1 OR `listLocations`.`idx`=:locationId) AND
(:areaId=-1 OR `listAreas`.`idx`=:areaId) AND
(:assetId=-1 OR `listAssets`.`idx`=:assetId) AND 
(:projectId=-1 OR `listProjects`.`idx`=:projectId) 
AND
CASE
	WHEN (DATE_FORMAT(:startDate, '%Y-%m-%d')='1985-08-09' AND DATE_FORMAT(:endDate, '%Y-%m-%d')='1985-08-09') OR (:startDate IS NULL AND :endDate IS NULL)
	THEN 1=1
	ELSE
	`listsalesorders`.`dateEntered` >= :startDate AND `listsalesorders`.`dateEntered` <= :endDate
END
AND 
(:ItemStatus = -1 OR `listsalesorders`.`status`= :ItemStatus)
AND
(:ShippingStatus =-1 OR `listsalesorders`.`shippingstatus`= :ShippingStatus)
ORDER BY `listCustomers`.`name`, `listLocations`.`name`, `listSalesOrders`.`number`

```

with the parameters -

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/4/e409322e422631ed8d8f64dc343f42e5900c29e8.png)

Is it really just a singular parameter that is causing the issue (the one at position 1 - ItemStatus or ShippingStatus?) or is there something with the structure of my NQ that is no longer acceptable in 8.1.26?

---

<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:** [April 18, 2023, 1:12pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569/2 "2023-04-18T13:12:51Z")

</div>

Not obviously buggy to me.

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [April 18, 2023, 1:31pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569/3 "2023-04-18T13:31:54Z")

</div>

Is there a more detailed stack trace over on the Gateway?

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [April 18, 2023, 1:37pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569/4 "2023-04-18T13:37:20Z")

</div>

I turned these to trace

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/8/98f21ef1e1b7fa47c477c2b7bc70a5f3534d7135.png)

Refreshed the table and I only see this in the gateway logs

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

No error appears.

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [April 18, 2023, 1:43pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569/5 "2023-04-18T13:43:53Z")

</div>

Oh, hmm. I think this could be a regression. Somebody recently tried to make it so named queries could have the `-- here's a comment` comment syntax in them, but the implementation was naive and if the query contained other instances of `--` (like yours does) it broke things...

If you replace those with another arbitrary character just to test does the query work again?

---

<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:** [April 18, 2023, 1:49pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569/6 "2023-04-18T13:49:13Z")

</div>

> [@Kevin.Herron](#):
>
> the implementation was naive

Yeah, I remember this. Trying to make Ignition support something (SQL scripts) that generic JDBC **doesn't** support. Because people are using it with drivers that do support it, but that breaks the conversion of colon-delimited NQ parameters into JDBC question-mark parameters.

Short of a true SQL parser in Ignition (!!), one group or the other will be unhappy.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [April 18, 2023, 1:51pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569/7 "2023-04-18T13:51:24Z")

</div>

Yea that must have been the cause, I replaced the "---" with "###" and now it is working as expected.

The customer really wants --- for blanks in the data so I guess the next question is is there a way for me to show a couple of hyphens without breaking things in Ignition within the 8.1.26 version but I suppose that will be database specific. Probably more suited for a different topic though. Thanks!

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [April 18, 2023, 1:53pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569/8 "2023-04-18T13:53:44Z")

</div>

Maybe `- - -` or some unicode alternative to `-`. Yuck.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [April 18, 2023, 1:58pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569/9 "2023-04-18T13:58:45Z")

</div>

That did solve the original query I posted about, but after figuring out the solution, I moved on to the next query with the same error and but it did not have a `--` anywhere -

```auto
SELECT 
sq.idx, 
sq.Flagged, 
sq.customerName, 
sq.locationName, 
sq.projectName, 
sq.number, 
sq.vendorName, 
sq.`Vendor Purchase Order`, 
sq.`Total Invoice Amount`, 
sq.`Total Amount Paid`, 
sq.sumOrdered, 
sq.totalOrdered, 
sq.sumConfirmed, 
sq.totalConfirmed, 
sq.sumReceived, 
sq.totalReceived,
sq.countBilled,
sq.countToEngr,
sq.countOkByEngr,
sq.countToAcct,
sq.countOkByAcct,
sq.countPaid,
sq.StatusNum, 
dvip.name as 'Payment Status', 
sq.createdDate
FROM 
(SELECT 
lvi.idx,
lvi.flag as 'Flagged',
lc.name as customerName, 
ll.name as locationName, 
lp.name as projectName, 
lvi.number,
lv.name as vendorName, 
lvp.vendorPONumber as 'Vendor Purchase Order',
SUM(lpi.unitcost)+SUM(lpi.taxAmount) as 'Total Invoice Amount',
lvip.amount as 'Total Amount Paid',
SUM(lpi.orderedButNotConfirmed) as sumOrdered,
COUNT(lpi.orderedButNotConfirmed) as totalOrdered,
SUM(lpi.confirmedByVendor) as sumConfirmed,
COUNT(lpi.confirmedByVendor) as totalConfirmed,
SUM(lpi.receivedByPSI) as sumReceived,
COUNT(lpi.receivedByPSI) as totalReceived,
SUM(ISNULL(lpi.billedToPSI)) as countBilled,
SUM(ISNULL(lpi.toEngr)) as countToEngr,
SUM(ISNULL(lpi.okByEngr)) as countOkByEngr,
SUM(ISNULL(lpi.toAcct)) as countToAcct,
SUM(ISNULL(lpi.okBYAcct)) as countOkByAcct,
SUM(ISNULL(lpi.paidByPSI)) as countPaid,
        (SELECT 
                (CASE
                        WHEN lvip.flag > 0
                		THEN 
                			4
                        WHEN
                            ((SUM(lpi.unitcost)+SUM(lpi.taxAmount) = lvip.amount
                                OR (SUM(lpi.unitcost)+SUM(lpi.taxAmount) = 0)))
                        THEN
                            1
                        WHEN (SUM(IFNULL(lvip.amount, 0)) = 0) THEN 2
                        WHEN (SUM(lpi.unitcost)+SUM(lpi.taxAmount) > IFNULL(lvip.amount, 0)) THEN 3
                        ELSE 2
                    END)
            ) AS 'StatusNum',
lvi.createdDate
FROM listvendorinvoices lvi
LEFT JOIN listvendorpurchaseorders lvp ON lvp.idx = lvi.parentVendorPOId
LEFT JOIN listvendors lv ON lv.idx = lvp.vendorId
LEFT JOIN listprojects lp ON lp.idx = lvp.projectId
LEFT JOIN listassets las ON las.idx = lp.parentAssetId
LEFT JOIN listareas la ON la.idx = las.parentAreaId
LEFT JOIN listlocations ll ON ll.idx = la.parentLocationId
LEFT JOIN listcustomers lc ON lc.idx = ll.parentCustomerId
LEFT JOIN listpartsinstances lpi ON lpi.billedToPSI = lvi.idx
LEFT JOIN (SELECT parentInvoiceId, SUM(amount) as amount, SUM(flag) as flag FROM listvendorinvoicepayments GROUP BY parentInvoiceId) as lvip ON lvi.idx = lvip.parentInvoiceId
WHERE 
(:vendorPoId =-1 OR lvi.parentVendorPOId=:vendorPoId) AND
(:invoiceNumber = 'All' OR lvi.number= :invoiceNumber) AND 
(:flag = -1 OR lvi.flag = :flag) AND
(:customerId=-1 OR lc.idx=:customerId) AND 
(:locationId=-1 OR ll.idx=:locationId) AND
(:areaId=-1 OR la.idx=:areaId) AND
(:assetId=-1 OR las.idx=:assetId) AND 
(:projectId=-1 OR lp.idx=:projectId) 
AND
CASE
	WHEN (DATE_FORMAT(:startDate, '%Y-%m-%d')='1985-08-09' AND DATE_FORMAT(:endDate, '%Y-%m-%d')='1985-08-09') OR (:startDate IS NULL AND :endDate IS NULL)
	THEN 1=1
	ELSE
	lvi.createdDate >= :startDate AND lvi.createdDate <= :endDate
END
GROUP BY lvp.idx, lvi.idx
HAVING
CASE 
	WHEN :orderedButNotConfirmed=0
	THEN sumOrdered!=totalOrdered OR sumOrdered IS NULL
	WHEN :orderedButNotConfirmed=1
	THEN sumOrdered=totalOrdered
	ELSE
	1=1
END
AND
CASE 
	WHEN :confirmedByVendor=0
	THEN sumConfirmed!=totalConfirmed OR sumConfirmed IS NULL
	WHEN :confirmedByVendor=1
	THEN sumConfirmed=totalConfirmed
	ELSE
	1=1
END
AND
CASE 
	WHEN :receivedByPSI=0
	THEN sumReceived!=totalReceived OR sumReceived IS NULL
	WHEN :receivedByPSI=1
	THEN sumReceived=totalReceived
	ELSE
	1=1
END
AND
CASE
    WHEN :billedToPSI=0
    THEN countBilled > 0
    WHEN :billedToPSI=1
    THEN countBilled=0
    ELSE
    1=1
END
AND
CASE
    WHEN :toEngr=0
    THEN countToEngr > 0
    WHEN :toEngr=1
    THEN countToEngr=0
    ELSE
    1=1
END
AND
CASE
    WHEN :okByEngr=0
    THEN countOkByEngr > 0
    WHEN :okByEngr=1
    THEN countOkByEngr=0
    ELSE
    1=1
END
AND
CASE
    WHEN :toAcct=0
    THEN countToAcct > 0
    WHEN :toAcct=1
    THEN countToAcct=0
    ELSE
    1=1
END
AND
CASE
    WHEN :okByAcct=0
    THEN countOkByAcct > 0
    WHEN :okByAcct=1
    THEN countOkByAcct=0
    ELSE
    1=1
END
AND
CASE
    WHEN :paidByPSI=0
    THEN countPaid > 0
    WHEN :paidByPSI=1
    THEN countPaid=0
    ELSE
    1=1
END) as sq
LEFT JOIN descvendorinvoicepaymentstatus dvip ON dvip.idx = sq.StatusNum
WHERE 
(:StatusNum=-1 OR sq.StatusNum=:StatusNum) 
ORDER BY sq.idx

```

params -

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/0/3095ac46033378367c44827bbe47f0a5feb92324.png)

Same error in client - `GatewayException: java.sql.SQLException: Could not set parameter at position 1 (values was -1)`

Same thing in Gateway log - just showing execution, no error appearing

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/a/4ae6b642715f7cc1a79becb26d6fa91c73418ea3.png)

I've already called into support and am awaiting a callback. But I guess there must be an additional issue.

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [April 18, 2023, 2:11pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569/10 "2023-04-18T14:11:58Z")

</div>

Hmm, the comment thing was the only recent change I could find or was aware of.

I'll have to let the support call find it's way to the right developer eventually. I think he's out today.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [April 18, 2023, 2:26pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569/11 "2023-04-18T14:26:13Z")

</div>

My fault - I was looking at the wrong named query on my previous post.

The issue was more `"--"` in the named queries. Adding a space in the middle was sufficient to fix the error. Thanks - I never would have figured this out.

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [April 18, 2023, 2:28pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569/12 "2023-04-18T14:28:35Z")

</div>

Phew!

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [April 18, 2023, 2:33pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569/13 "2023-04-18T14:33:58Z")

</div>

Just because I know I will be asked - is this considered a bug that will be changed in some future version, or something I should be considerate of in my named queries going forward?

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [April 18, 2023, 2:35pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569/14 "2023-04-18T14:35:31Z")

</div>

We're discussing this now.

I'm advocating for reverting the original "fix" and not introducing a new one unless it's done correctly with a SQL parser.

Either way, it seems like it's obviously a bug, and I'm hoping it's at least reverted by the next release.

The problem is somebody out there obviously has statements with comments that will be broken after the revert...

---

<div class="post-metadata">

**Author:** ![kcollins](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kcollins/32/19646_2.png) [@kcollins](https://forum.inductiveautomation.com/u/kcollins)\
**Post date:** [April 18, 2023, 7:38pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569/15 "2023-04-18T19:38:43Z")

</div>

> [@bkarabinchak.psi](#):
>
> The customer really wants --- for blanks in the data

I know I'm late to the party, and it is hacky, but perhaps you could use something like:

`CONCAT('-', '-', '-')`

...as the field value instead of the static `---` as a functionally-equivalent workaround. 🤷

---

<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:** [July 14, 2024, 10:12pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569/16 "2024-07-14T22:12:08Z")

</div>

This has just tripped us up upgrading from 8.1.25 to 8.1.39..... I have quite a few other gateways to update, would you be able to identify which versions would have this regression? I have the following list of versions I'd like to upgrade to 8.1.39.

8.1.17 / 21 / 25 / 27 / 30

I will search through all the NQ's and look for '--' and remove them before any upgrades. But would be great to know the above please.

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [July 14, 2024, 11:15pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569/17 "2024-07-14T23:15:33Z")

</div>

It looks like the regression was introduced in 8.1.23 and fixed in 8.1.28.

---

<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:** [July 14, 2024, 11:28pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569/18 "2024-07-14T23:28:24Z")

</div>

Thank you, Kevin.

---

<div class="post-metadata">

**Author:** ![william.pitcher](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/william.pitcher/32/83283_2.png) [@william.pitcher](https://forum.inductiveautomation.com/u/william.pitcher)\
**Post date:** [May 9, 2025, 5:45pm UTC](https://forum.inductiveautomation.com/t/upgraded-from-8-1-17-to-8-1-26-now-named-query-issue-cannot-set-parameter-at-position-1-values-was-1/72569/19 "2025-05-09T17:45:57Z")

</div>

I just added a new named query and ran into this issue using the standard oracle driver. There seems to be an issue with designer not and IN direction when adding parameters to the oracle command object.

I was able to remedy the issue by deleting all the parameters and recreating them.

Version: 8.1.30