# Perspective Dropdown SQL Query

**URL:** <https://forum.inductiveautomation.com/t/perspective-dropdown-sql-query/41172>\
**Category:** Ignition\
**Tags:** perspective, scripting, ignition80\
**Created:** [December 3, 2020, 2:47pm UTC](https://forum.inductiveautomation.com/t/perspective-dropdown-sql-query/41172 "2020-12-03T14:47:52Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Gaurav.Shitole](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/gaurav.shitole/32/72050_2.png) [@Gaurav.Shitole](https://forum.inductiveautomation.com/u/Gaurav.Shitole)\
**Post date:** [December 3, 2020, 2:47pm UTC](https://forum.inductiveautomation.com/t/perspective-dropdown-sql-query/41172/1 "2020-12-03T14:47:52Z")

</div>

Hi there,

I need to populate dropdown with SQL query and I did it using named query, it returns in dataset. However I converted it to json so that my WHERE clause would work. With the datasets, it was showing me an errors.

See below screenshot:

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

Here in the WHERE clause if I pass a single value, it works but I want to pass a values like 1,2,3, so on. As seen in screenshot, when I pass another value it shows me an error. Basically this is how it should work:

I have tons of recipes with ID being primary key and another column is recipe\_name. I am not showing all recipes for a select in dropdown, I am passing certain values based on certain selections. Like below:

```auto
SELECT recipe_id as value, recipe_name as label FROM recipe WHERE recipe_id IN (hereVariableListComes)

```

This works well with vision but I am facing some erros here. please suggest

Ignition version: v8.1  
OS: Windows 10, 64 bit

---

<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:** [December 3, 2020, 3:02pm UTC](https://forum.inductiveautomation.com/t/perspective-dropdown-sql-query/41172/2 "2020-12-03T15:02:11Z")

</div>

This was answered in the other thread. Use a script with runPrepQuery, generating SQL with a suitable number of ? substitutions. You can't just pass a tuple into a query. At least, not without massaging its string representation into something your SQL server will tolerate.

> [@Mouse Entered or Mouse moved events not working in Client](http://forum.inductiveautomation.com/t/mouse-entered-or-mouse-moved-events-not-working-in-client/41024/4):
>
> JDBC does not generally support passing a list as a single parameter. Using the IN operator generally requires the use of runPrepQuery where the appropriate number of question marks are placed inside the operator’s parenthesis, and the argument list constructed to match the number of question marks. Sorry.

---

<div class="post-metadata">

**Author:** ![Gaurav.Shitole](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/gaurav.shitole/32/72050_2.png) [@Gaurav.Shitole](https://forum.inductiveautomation.com/u/Gaurav.Shitole)\
**Post date:** [December 4, 2020, 2:28pm UTC](https://forum.inductiveautomation.com/t/perspective-dropdown-sql-query/41172/3 "2020-12-04T14:28:25Z")

</div>

Thanks alot @pturmel!

I did it the way you said this time. I did it in the transform and it all worked out.  
Is there any better way to achive this than this?

```auto
	pyData = system.dataset.toPyDataSet(system.tag.read("[projName]Global/ReleasedMasterRecipes").value)
	pyDataL = []
	rowCount = len(pyData)
	placeholders = ",".join(list("?"*rowCount))
	
	query = "SELECT recipe_id, recipe_name FROM recipe WHERE recipe_id IN ({})".format(placeholders)
	
	for row in pyData:
		pyDataL.append(row[0])
		
	value = system.db.runPrepQuery(query,pyDataL)
	return value

```

This works out but little bit confused if there is any better way to do that pyDataL list I am making

---

<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:** [December 4, 2020, 3:42pm UTC](https://forum.inductiveautomation.com/t/perspective-dropdown-sql-query/41172/4 "2020-12-04T15:42:04Z")

</div>

You can use list comprehension for this:

```auto
for row in pyData:
		pyDataL.append(row[0])

```

Becomes:

```auto
pyDataL = [row[0] for row in pyData] 

```

---

<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:** [December 4, 2020, 6:25pm UTC](https://forum.inductiveautomation.com/t/perspective-dropdown-sql-query/41172/5 "2020-12-04T18:25:50Z")

</div>

The native Dataset type (straight out of the tag) also has a .getColumnAsList() method that would be even simpler. No conversion to a PyDataset nor any loop.

---

<div class="post-metadata">

**Author:** ![Gaurav.Shitole](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/gaurav.shitole/32/72050_2.png) [@Gaurav.Shitole](https://forum.inductiveautomation.com/u/Gaurav.Shitole)\
**Post date:** [December 4, 2020, 6:29pm UTC](https://forum.inductiveautomation.com/t/perspective-dropdown-sql-query/41172/6 "2020-12-04T18:29:29Z")

</div>

Will try this  
Also thanks @nminchin