# Running a named query with python - help

**URL:** <https://forum.inductiveautomation.com/t/running-a-named-query-with-python-help/96658>\
**Category:** Ignition\
**Tags:** ignition81, perspective\
**Created:** [October 1, 2024, 1:23pm UTC](https://forum.inductiveautomation.com/t/running-a-named-query-with-python-help/96658 "2024-10-01T13:23:30Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [October 1, 2024, 1:23pm UTC](https://forum.inductiveautomation.com/t/running-a-named-query-with-python-help/96658/1 "2024-10-01T13:23:30Z")

</div>

I am using this code:

```python
value = 5187-1
params = {'SpecNum': value}
results = system.db.runNamedQuery('Spec/Full Spec', params)
return results

```

to run a named query. For test purposes value is hard coded in, but will be replaced with at variable tag read.  
I am getting an error: File input, line 3 - index 2 is out of range

Here is the nameQuery:

```python
SELECT SpecPoint.Descr AS 'Description',
	SpecPoint.ScalarValue as 'Value',
	SpecPointLimit.LowerLimit As 'Minimum',
	SpecPointLimit.UpperLimit as 'Maximum'
--,SpecPointLimit.LowerLimit, SpecPointLimit.UpperLimit, 
--, Spec.PlantID
--,SpecPoint.SpecID, SpecPointLimit.ID, Spec.ID
FROM SpecPoint
  INNER JOIN Spec ON SpecPoint.SpecID = Spec.ID
  INNER JOIN SpecPointLimit ON SpecPoint.ID = SpecPointLimit.SpecPointID
-- INNER JOIN ProcessType ON Spec.ProcessTypeID = ProcessType.ID
  INNER JOIN SpecProduct ON SpecPoint.SpecID = SpecProduct.SpecID
  INNER JOIN Product ON SpecProduct.ProductGUID = Product.GUID--,
-- Reference,
-- SpecNote
  
WHERE :SpecNum --SpecNbr + '-' + CAST(RevisionNbr AS VARCHAR) = :SpecNum --Product.Number = :SpecNum
AND Spec.StatusLevelID = 2
--AND SpecPoint.ScalarValue = 100
AND Spec.PlantID = 210

ORDER BY SpecPoint.Descr ASC;

```

I will remove the commented out parts when I finish the query.  
Any ideas on why I have an index error?

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [October 1, 2024, 1:25pm UTC](https://forum.inductiveautomation.com/t/running-a-named-query-with-python-help/96658/2 "2024-10-01T13:25:26Z")

</div>

I would remove the commented out lines now and try it again.

They can cause issues with the JDBC driver.

---

<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:** [October 1, 2024, 1:39pm UTC](https://forum.inductiveautomation.com/t/running-a-named-query-with-python-help/96658/3 "2024-10-01T13:39:46Z")

</div>

Do you really mean to perform subtraction when assigning to `value` ? If not, you need quotes.

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [October 1, 2024, 1:53pm UTC](https://forum.inductiveautomation.com/t/running-a-named-query-with-python-help/96658/4 "2024-10-01T13:53:47Z")

</div>

Alright, I have made both of the changes by lrose and pturmel. I looks like it tried to run the query and I get a new error:

```python
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.microsoft.sqlserver.jdbc.SQLServerException: An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.

```

---

<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:** [October 1, 2024, 1:55pm UTC](https://forum.inductiveautomation.com/t/running-a-named-query-with-python-help/96658/5 "2024-10-01T13:55:06Z")

</div>

Post your new query in full.

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [October 1, 2024, 1:58pm UTC](https://forum.inductiveautomation.com/t/running-a-named-query-with-python-help/96658/6 "2024-10-01T13:58:41Z")

</div>

I saw the error. My query in full:

```python
SELECT SpecPoint.Descr AS 'Description',
	SpecPoint.ScalarValue as 'Value',
	SpecPointLimit.LowerLimit As 'Minimum',
	SpecPointLimit.UpperLimit as 'Maximum'

FROM SpecPoint
  INNER JOIN Spec ON SpecPoint.SpecID = Spec.ID
  INNER JOIN SpecPointLimit ON SpecPoint.ID = SpecPointLimit.SpecPointID
  INNER JOIN SpecProduct ON SpecPoint.SpecID = SpecProduct.SpecID
  INNER JOIN Product ON SpecProduct.ProductGUID = Product.GUID--,
  
WHERE SpecNbr + '-' + CAST(RevisionNbr AS VARCHAR) = :SpecNum 
AND Spec.StatusLevelID = 2
AND Spec.PlantID = 210

ORDER BY SpecPoint.Descr ASC;

```

I forgot to add the columns/rows to look for with the parameter. I had just the parameter:

```python
WHERE SpecNbr + '-' + CAST(RevisionNbr AS VARCHAR) = :SpecNum 

```

before i had just

```python
:SpecNum

```

Thanks for talking me through it,

---

<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:** [October 1, 2024, 2:01pm UTC](https://forum.inductiveautomation.com/t/running-a-named-query-with-python-help/96658/7 "2024-10-01T14:01:39Z")

</div>

Consider passing SpecNbr and RevisionNbr separately, with separate comparisions in the WHERE. Casting and concatenating are not efficient. You probably want to make sure to have indices on some or all of the columns you use in the WHERE clause, or this won't scale.