# Trouble with IN clause of named query with MSSQL

**URL:** <https://forum.inductiveautomation.com/t/trouble-with-in-clause-of-named-query-with-mssql/88288>\
**Category:** Ignition\
**Tags:** ignition79\
**Created:** [April 3, 2024, 2:20pm UTC](https://forum.inductiveautomation.com/t/trouble-with-in-clause-of-named-query-with-mssql/88288 "2024-04-03T14:20:27Z")\
**Posts on this page:** 2\
**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 3, 2024, 2:20pm UTC](https://forum.inductiveautomation.com/t/trouble-with-in-clause-of-named-query-with-mssql/88288/1 "2024-04-03T14:20:28Z")

</div>

Using 7.9.21.

I am trying to make a report with the following functionality -  
if no users are provided, I run the report data as usual, but if users are provided, I only grab rows relating to said users.

I have users as a single column dataset with test like so -

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

This default expression works I can see it as a preview on the report if dragged and dropped.

My SQL looks like

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

```auto
SELECT [idx], [DPD_Group], [DPD_Project], [Project], [Project_Status], [Hot_Topic], [timeStamp], [userid]
FROM dpd.project_status_table 
WHERE [last_curator_review_tstamp] BETWEEN :startDate AND :endDate 
AND [Hot_Topic] = :hotTopic 
AND [assetType] = :assetType 
AND (:dpdProject=-1 OR [DPD_Project]=:dpdProject)
AND (:userIdLength=0 OR [userid] IN ({users}))
order by 'Project', 'timeStamp' DESC

```

My datasource is a script

```auto
	assets = ['Milestones', 'Hot Topics', 'Emerging Issues']
	for asset in assets:
		userStr = ", ".join(["'%s'"%(user) for (user,) in system.dataset.toPyDataSet(data['users'])])
		params = {'startDate':data['StartDate'], 'endDate':data['EndDate'], 'assetType':asset, 'dpdProject':data['dpdProject'], 'userIdLength':data['users'].rowCount, 'users':userStr}
		data[asset] = system.db.runNamedQuery('Report/reportData', params)

```

When I go to preview my report I get this error -

```auto
WARN: Error invoking script.Traceback (most recent call last):
  File "<function:updateData>", line 6, in updateData
	at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.runNamedQuery(GatewayDBUtilities.java:354)

	at sun.reflect.GeneratedMethodAccessor87.invoke(Unknown Source)

	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.lang.reflect.Method.invoke(Unknown Source)

java.lang.NullPointerException: java.lang.NullPointerException

```

Where line 6 is where I call my named query.

This didn't seem very helpful to me so I tried running this named query in script console with the following -

```auto
users = system.dataset.toDataSet(["Users"],[["User"],["Curator"]])
data ={
	"EndDate":system.date.now(),
	"StartDate":system.date.addDays(system.date.now(),-7),
	"dpdProject":-1,
	"users":users
}

assets = ['Milestones', 'Hot Topics', 'Emerging Issues']
for asset in assets:
	userStr = ", ".join(["'%s'"%(user) for (user,) in system.dataset.toPyDataSet(data['users'])])
	print userStr
	params = {'startDate':data['StartDate'], 'endDate':data['EndDate'], 'assetType':asset, 'dpdProject':data['dpdProject'], 'userIdLength':data['users'].rowCount, 'users':userStr}
	print params
	data[asset] = system.db.runNamedQuery('DPD Weeklies Report V2', params)
print data

```

The script console worked.

```auto
'User', 'Curator'
{'endDate': Wed Apr 03 10:16:27 EDT 2024, 'users': u"'User', 'Curator'", 'assetType': 'Milestones', 'userIdLength': 2, 'startDate': Wed Mar 27 10:16:27 EDT 2024, 'dpdProject': -1}
{'EndDate': Wed Apr 03 10:23:47 EDT 2024, 'Emerging Issues': Dataset [0R ? 8C], 'users': Dataset [2R ? 1C], 'StartDate': Wed Mar 27 10:23:47 EDT 2024, 'Milestones': Dataset [0R ? 8C], 'Hot Topics': Dataset [0R ? 8C], 'dpdProject': -1}

```

and this worked

Then I tested my named query directly and that does seem to work though

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

I can't tell what these error indicates so I don't see what I am doing wrong especially since the SQL is valid when tested directly. What is going on?

---

<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 3, 2024, 2:25pm UTC](https://forum.inductiveautomation.com/t/trouble-with-in-clause-of-named-query-with-mssql/88288/2 "2024-04-03T14:25:05Z")

</div>

Reports run on gateway. I needed a project name in my system.db.runNamedQuery.