# Getting Unique Values From Database with Scripting

**URL:** <https://forum.inductiveautomation.com/t/getting-unique-values-from-database-with-scripting/78640>\
**Category:** Ignition\
**Tags:** perspective, scripting\
**Created:** [August 31, 2023, 7:10pm UTC](https://forum.inductiveautomation.com/t/getting-unique-values-from-database-with-scripting/78640 "2023-08-31T19:10:21Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Pico\_Sankari](https://avatars.discourse-cdn.com/v4/letter/p/aeb1de/32.png) [@Pico\_Sankari](https://forum.inductiveautomation.com/u/Pico_Sankari)\
**Post date:** [August 31, 2023, 7:10pm UTC](https://forum.inductiveautomation.com/t/getting-unique-values-from-database-with-scripting/78640/1 "2023-08-31T19:10:21Z")

</div>

I'm trying to use scripting to get the unique values of a tag from a database. In the Database Query Browser this line works and gets the data I want:  
`SELECT distinct "RecipeName" FROM WaxPressParts`

This doesn't work when I try to add it to scripting for a component:

```auto
    queryBase = "SELECT distinct RecipeName FROM WaxPressParts "
    queryClause = "WHERE (CONVERT(varchar(25), t_stamp, 126) >= ? AND CONVERT(varchar(25), t_stamp, 126) < ?)"
    queryText = queryBase + queryClause + " ORDER BY t_stamp DESC"
    self.props.data = system.db.runPrepQuery(queryText)

```

This gives a source error. Replacing 'distinct' with 'top 1000' gets rid of errors and returns the top 1000 results so I know that the rest of the script is good and 'distinct' is causing the issue. I've also tried 'unique' instead of 'distinct' and am getting the same issue. Is there a good way to get the unique values from a database while scripting on a component?

---

<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:** [August 31, 2023, 7:12pm UTC](https://forum.inductiveautomation.com/t/getting-unique-values-from-database-with-scripting/78640/2 "2023-08-31T19:12:50Z")

</div>

You're not passing any actual parameters in to `runPrepQuery`? You've got two parameters indicated (`?`) but `runPrepQuery` takes an additional argument for those parameters that's not being supplied in your excerpt.

As a tangent, consider using a triple quoted string to avoid unpleasant concatentation:

```python
    query = """
        SELECT distinct RecipeName FROM WaxPressParts 
        WHERE (CONVERT(varchar(25), t_stamp, 126) >= ? AND CONVERT(varchar(25), t_stamp, 126) < ?)
        ORDER BY t_stamp DESC
    """
    self.props.data = system.db.runPrepQuery(queryText)

```

---

<div class="post-metadata">

**Author:** ![Pico\_Sankari](https://avatars.discourse-cdn.com/v4/letter/p/aeb1de/32.png) [@Pico\_Sankari](https://forum.inductiveautomation.com/u/Pico_Sankari)\
**Post date:** [August 31, 2023, 7:26pm UTC](https://forum.inductiveautomation.com/t/getting-unique-values-from-database-with-scripting/78640/3 "2023-08-31T19:26:32Z")

</div>

I tried the triple quotes and got the same error. I'm modifying an older table that someone else initially set up and that uses the question marks. I tried running the query without the WHERE statement and also got the same error. Given that the top 1000 query worked just fine, I don't think the question mark line is an issue. Is there a good way to trouble shoot the 'unknown source' error?

---

<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:** [August 31, 2023, 7:33pm UTC](https://forum.inductiveautomation.com/t/getting-unique-values-from-database-with-scripting/78640/4 "2023-08-31T19:33:16Z")

</div>

Well, could you paste the actual stacktrace/full details here?

---

<div class="post-metadata">

**Author:** ![Pico\_Sankari](https://avatars.discourse-cdn.com/v4/letter/p/aeb1de/32.png) [@Pico\_Sankari](https://forum.inductiveautomation.com/u/Pico_Sankari)\
**Post date:** [August 31, 2023, 7:50pm UTC](https://forum.inductiveautomation.com/t/getting-unique-values-from-database-with-scripting/78640/5 "2023-08-31T19:50:58Z")

</div>

This is the error:

```auto
com.inductiveautomation.ignition.common.script.JythonExecException
Traceback (most recent call last):
  File "<function:runAction>", line 2, in runAction
  File "<custom-method getDies>", line 12, in getDies
            SELECT distinct RecipeName FROM WaxPressParts 
        WHERE (CONVERT(varchar(25), t_stamp, 126) >= ? AND CONVERT(varchar(25), t_stamp, 126) < ?) ORDER BY t_stamp DESC, AutomationData, [2023-08-10%, 2023-08-19%], )

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepQuery(
            SELECT distinct RecipeName FROM WaxPressParts 
        WHERE (CONVERT(varchar(25), t_stamp, 126) >= ? AND CONVERT(varchar(25), t_stamp, 126) < ?) ORDER BY t_stamp DESC, AutomationData, [2023-08-10%, 2023-08-19%], )

	caused by org.python.core.PyException
Traceback (most recent call last):
  File "<function:runAction>", line 2, in runAction
  File "<custom-method getDies>", line 12, in getDies
            SELECT distinct RecipeName FROM WaxPressParts 
        WHERE (CONVERT(varchar(25), t_stamp, 126) >= ? AND CONVERT(varchar(25), t_stamp, 126) < ?) ORDER BY t_stamp DESC, AutomationData, [2023-08-10%, 2023-08-19%], )

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepQuery(
            SELECT distinct RecipeName FROM WaxPressParts 
        WHERE (CONVERT(varchar(25), t_stamp, 126) >= ? AND CONVERT(varchar(25), t_stamp, 126) < ?) ORDER BY t_stamp DESC, AutomationData, [2023-08-10%, 2023-08-19%], )

	caused by Exception: Error executing system.db.runPrepQuery(
            SELECT distinct RecipeName FROM WaxPressParts 
        WHERE (CONVERT(varchar(25), t_stamp, 126) >= ? AND CONVERT(varchar(25), t_stamp, 126) < ?) ORDER BY t_stamp DESC, AutomationData, [2023-08-10%, 2023-08-19%], )
	caused by SQLServerException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Ignition v8.1.23 (b2022121308)
Java: Azul Systems, Inc. 11.0.16.1

```

---

<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:** [August 31, 2023, 7:54pm UTC](https://forum.inductiveautomation.com/t/getting-unique-values-from-database-with-scripting/78640/6 "2023-08-31T19:54:24Z")

</div>

SQLServer is telling you what's wrong:

> [@Pico\_Sankari](#):
>
> `SQLServerException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.`