# Query returns no rows when using tag value as argument

**URL:** <https://forum.inductiveautomation.com/t/query-returns-no-rows-when-using-tag-value-as-argument/42678>\
**Category:** Ignition\
**Created:** [January 27, 2021, 9:48pm UTC](https://forum.inductiveautomation.com/t/query-returns-no-rows-when-using-tag-value-as-argument/42678 "2021-01-27T21:48:49Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![kelly.draehn](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kelly.draehn/32/21474_2.png) [@kelly.draehn](https://forum.inductiveautomation.com/u/kelly.draehn)\
**Post date:** [January 27, 2021, 9:48pm UTC](https://forum.inductiveautomation.com/t/query-returns-no-rows-when-using-tag-value-as-argument/42678/1 "2021-01-27T21:48:49Z")

</div>

Hi All!

I am attempting to use a scalarPrepQuery with one argument in the where clause:

PN = str(system.tag.readBlocking(["[default]CurrentPartNumber"])[0].value)

query\_lower = “”“SELECT TOP (1)  
[LOWER\_SPEC]  
FROM [Test].[dbo].[GET\_SPEC]  
WHERE EQUIP\_NUM = ‘XXX’ AND PART\_NUMBER = ?  
ORDER BY TIMESTAMP DESC”""

lowerSpec = system.db.runScalarPrepQuery(query\_lower,[PN])

And it returns no results.

The weird thing is, if I instead type the returned tag value into the PN variable like this:

PN = ‘PartNumber’

And then run it, it returns the results I’m looking for.

Has anyone else seen this? I checked the type and both show as strings and if you print them out (the tag value and the manually typed in one) they both look identical

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [January 27, 2021, 9:53pm UTC](https://forum.inductiveautomation.com/t/query-returns-no-rows-when-using-tag-value-as-argument/42678/2 "2021-01-27T21:53:51Z")

</div>

Try this

```auto
PN = system.tag.readBlocking(["[default]CurrentPartNumber"])[0].value

query_lower = '''SELECT TOP (1)
                 [LOWER_SPEC]
                 FROM [Test].[dbo].[GET_SPEC]
                 WHERE EQUIP_NUM = "XXX" AND PART_NUMBER = "?"
                 ORDER BY TIMESTAMP DESC
'''

lowerSpec = system.db.runScalarPrepQuery(query_lower,[PN])

```

---

<div class="post-metadata">

**Author:** ![kelly.draehn](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kelly.draehn/32/21474_2.png) [@kelly.draehn](https://forum.inductiveautomation.com/u/kelly.draehn)\
**Post date:** [January 27, 2021, 10:02pm UTC](https://forum.inductiveautomation.com/t/query-returns-no-rows-when-using-tag-value-as-argument/42678/3 "2021-01-27T22:02:17Z")

</div>

I get an index 1 is out of range exception?

Caused by: com.inductiveautomation.ignition.client.gateway\_interface.GatewayException: The index 1 is out of range.

```
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:359)

at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:333)

at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:286)

at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:868)

at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runQuery(GatewayInterface.java:839)

at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runPrepQuery(ClientDBUtilities.java:317)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runScalarPrepQuery(AbstractDBUtilities.java:308)

... 23 more
```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [January 27, 2021, 10:08pm UTC](https://forum.inductiveautomation.com/t/query-returns-no-rows-when-using-tag-value-as-argument/42678/4 "2021-01-27T22:08:42Z")

</div>

hmm…try this. If this doesn’t work, isolate the `PN=` line and add `print PN` to make sure the value you are getting is what you expect

```auto
PN = system.tag.readBlocking(["[default]CurrentPartNumber"])[0].value

query_lower = '''SELECT TOP (1)
                 [LOWER_SPEC]
                 FROM [Test].[dbo].[GET_SPEC]
                 WHERE EQUIP_NUM = "XXX" AND PART_NUMBER = ?
                 ORDER BY TIMESTAMP DESC
'''

lowerSpec = system.db.runScalarPrepQuery(query_lower,[PN])

```

```auto
PN = system.tag.readBlocking(["[default]CurrentPartNumber"])[0].value
print PN

```

---

<div class="post-metadata">

**Author:** ![WillMT10](https://avatars.discourse-cdn.com/v4/letter/w/7feea3/32.png) [@WillMT10](https://forum.inductiveautomation.com/u/WillMT10)\
**Post date:** [January 27, 2021, 10:33pm UTC](https://forum.inductiveautomation.com/t/query-returns-no-rows-when-using-tag-value-as-argument/42678/5 "2021-01-27T22:33:05Z")

</div>

I’d recommend `print repr(PN)` so you can see any white spaces or hidden characters.

---

<div class="post-metadata">

**Author:** ![kelly.draehn](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kelly.draehn/32/21474_2.png) [@kelly.draehn](https://forum.inductiveautomation.com/u/kelly.draehn)\
**Post date:** [January 28, 2021, 2:35pm UTC](https://forum.inductiveautomation.com/t/query-returns-no-rows-when-using-tag-value-as-argument/42678/6 "2021-01-28T14:35:26Z")

</div>

WillMT10 gets the prize! I didn’t know about repr and print wasn’t showing anything.

Thank you!

I’m getting this:

“u’PartNumber\r\x00’”

Is there an ideal way to get rid this? Just use spring parsing to delete the first and last ones? Or is there a more elegant way to drop hidden characters?

---

<div class="post-metadata">

**Author:** ![kelly.draehn](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kelly.draehn/32/21474_2.png) [@kelly.draehn](https://forum.inductiveautomation.com/u/kelly.draehn)\
**Post date:** [January 28, 2021, 2:40pm UTC](https://forum.inductiveautomation.com/t/query-returns-no-rows-when-using-tag-value-as-argument/42678/7 "2021-01-28T14:40:41Z")

</div>

Okay I did a google and came up with this:

PN = re.sub("[^a-z0-9]+","", PN, flags=re.IGNORECASE)

Figured I’d ask if this is how y’all do it or if there is an ignition library that is better. But either way seems to be working!!

Thank u for the help!  
-Kelly

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 28, 2021, 4:13pm UTC](https://forum.inductiveautomation.com/t/query-returns-no-rows-when-using-tag-value-as-argument/42678/8 "2021-01-28T16:13:06Z")

</div>

If the format is always the same (with a CR after the part number), you can use split():

```python
s = u'PartNumber\r\x00'
print repr(s)

t = s.split('\r')
print t
print repr(t[0])

```

Output:

```nohighlight
u'PartNumber\r\x00'
[u'PartNumber', u'\x00']
u'PartNumber'
>>>

```