# Variable in SQL query

**URL:** <https://forum.inductiveautomation.com/t/variable-in-sql-query/31031>\
**Category:** Ignition\
**Created:** [January 3, 2020, 3:31am UTC](https://forum.inductiveautomation.com/t/variable-in-sql-query/31031 "2020-01-03T03:31:47Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![chaoliang](https://avatars.discourse-cdn.com/v4/letter/c/ba8739/32.png) [@chaoliang](https://forum.inductiveautomation.com/u/chaoliang)\
**Post date:** [January 3, 2020, 3:31am UTC](https://forum.inductiveautomation.com/t/variable-in-sql-query/31031/1 "2020-01-03T03:31:47Z")

</div>

I am trying to configure a dataset to read data from a table.  
The scripting should be like the following:  
SELECT t\_stamp, FIC01\_SP, FIC01\_PV, FIC01\_CV FROM trend\_pid

Because it’s a faceplate trend dataset, the FIC01 should be a variable, able to change to FIC02…FIC0n.

I then changed the query to  
SELECT t\_stamp, {Root Container.TAG\_NAME}+’\_SP’,{Root Container.TAG\_NAME}+’\_PV’,{Root Container.TAG\_NAME}+’\_CV’ FROM trend\_pid  
But it won’t work.

Can anyone advise what’s the proper way to assign variable in SQL str and how to comibne two string in a SQL query?

---

<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:** [January 3, 2020, 7:03am UTC](https://forum.inductiveautomation.com/t/variable-in-sql-query/31031/2 "2020-01-03T07:03:09Z")

</div>

Not at PC, but Im pretty sure you just add the indirect part directly.  
Eg.  
SELECT {Root Container.TAG\_NAME}\_SP FROM…

---

<div class="post-metadata">

**Author:** ![chaoliang](https://avatars.discourse-cdn.com/v4/letter/c/ba8739/32.png) [@chaoliang](https://forum.inductiveautomation.com/u/chaoliang)\
**Post date:** [January 5, 2020, 10:37pm UTC](https://forum.inductiveautomation.com/t/variable-in-sql-query/31031/3 "2020-01-05T22:37:21Z")

</div>

Thank you for the help.

It worked out very well.

One further question is:  
How to add expression in SQL Query?  
I want to query the following string, but it won’t work:  
SELECT t\_stamp, {Root Container.TAG\_NAME}\_SP, {Root Container.TAG\_NAME}\_PV  
FROM trend\_pid  
WHERE t\_stamp \> dateArithmetic(now(), -1, “hr”)

---

<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:** [January 5, 2020, 11:00pm UTC](https://forum.inductiveautomation.com/t/variable-in-sql-query/31031/4 "2020-01-05T23:00:32Z")

</div>

Add a custom property to your root container to compute the date expression. You probably want to use the dateFormat() expression to force the format to something compatible with your DB (default conversion from java.util.Date isn’t acceptable to many DB flavors). Then your query becomes:

```auto
SELECT t_stamp, {Root Container.TAG_NAME}_SP, {Root Container.TAG_NAME}_PV
FROM trend_pid
WHERE t_stamp > '{Root Container.OneHourAgo}'

```

---

<div class="post-metadata">

**Author:** ![chaoliang](https://avatars.discourse-cdn.com/v4/letter/c/ba8739/32.png) [@chaoliang](https://forum.inductiveautomation.com/u/chaoliang)\
**Post date:** [January 5, 2020, 11:11pm UTC](https://forum.inductiveautomation.com/t/variable-in-sql-query/31031/5 "2020-01-05T23:11:30Z")

</div>

Thank you. It is a good solution.

---

<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:** [January 6, 2020, 1:24pm UTC](https://forum.inductiveautomation.com/t/variable-in-sql-query/31031/6 "2020-01-06T13:24:48Z")

</div>

You could also just use the SQL language to do this as well, for example:

```auto
-- SQL server
DATEADD(HOUR, -1, GETDATE()) 

```