# Valid SQL statements in Ignition (JDBC)

**URL:** <https://forum.inductiveautomation.com/t/valid-sql-statements-in-ignition-jdbc/25259>\
**Category:** Ignition\
**Created:** [May 23, 2019, 11:51am UTC](https://forum.inductiveautomation.com/t/valid-sql-statements-in-ignition-jdbc/25259 "2019-05-23T11:51:29Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Esn](https://avatars.discourse-cdn.com/v4/letter/e/91b2a8/32.png) [@Esn](https://forum.inductiveautomation.com/u/Esn)\
**Post date:** [May 23, 2019, 11:51am UTC](https://forum.inductiveautomation.com/t/valid-sql-statements-in-ignition-jdbc/25259/1 "2019-05-23T11:51:29Z")

</div>

Hi,

From another topic I got this quote:

> SQL statements in Ignition are run through JDBC driver(s), which generally speaking do not support more complex queries/statements

Is there a place where I can see which statements are valid and their syntax?

I'm not very familiar with SQL and I just need to create a simple query that picks between 2 different queries based on a condition.  
I found some different IF..ELSE SQL statements, but I could'nt get them working.  
Is an IF..ELSE statement supoprted?  
If yes how does the syntax look?

Thanks

---

<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:** [May 23, 2019, 12:09pm UTC](https://forum.inductiveautomation.com/t/valid-sql-statements-in-ignition-jdbc/25259/2 "2019-05-23T12:09:06Z")

</div>

Standard SQL uses “[CASE … END](https://www.postgresql.org/docs/9.5/functions-conditional.html)” with various forms of IF clauses within. That isn’t really a JDBC issue, but the syntax flavor of your connected DB. The issue in Ignition is the identification of UPDATE/INSERT operations versus SELECT operations. Queries that look like neither often don’t work everywhere. Queries submitted via scripting functions that supply that information are more tolerant. (Like system.db.runPrepQuery() versus system.db.runPrepUpdate()).

---

<div class="post-metadata">

**Author:** ![Esn](https://avatars.discourse-cdn.com/v4/letter/e/91b2a8/32.png) [@Esn](https://forum.inductiveautomation.com/u/Esn)\
**Post date:** [May 23, 2019, 1:17pm UTC](https://forum.inductiveautomation.com/t/valid-sql-statements-in-ignition-jdbc/25259/3 "2019-05-23T13:17:13Z")

</div>

@pturmel  
Thanks.  
I’ve tried this kind of SQL statement now, but it’s not working:

```auto
select value from valueTable where [key] =
     case
          when {[.]UDTDATA/AREA} = '1' then concat( 'SP1.' , {[.]UDTDATA/SITE} , '.REF' )
          when {[.]UDTDATA/AREA} = '2' then concat( 'SP2.' , {[.]UDTDATA/SITE} , '.REF' )
     end

```

Edit: I should mention that this is code in a query tag inside a UDT.

---

<div class="post-metadata">

**Author:** ![Sanderd17](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/sanderd17/32/8735_2.png) [@Sanderd17](https://forum.inductiveautomation.com/u/Sanderd17)\
**Post date:** [May 23, 2019, 2:29pm UTC](https://forum.inductiveautomation.com/t/valid-sql-statements-in-ignition-jdbc/25259/4 "2019-05-23T14:29:27Z")

</div>

You will probably have to troubleshoot it via trial and error.

F.e. try that query in the “Database Query Browser” tool (with the tag values replaced), check what it returns. If it doesn’t work, try to remove or simplify parts of the query until it works (f.e. remove the concats, remove the case, …). If it works in the dataabase browser, there’s probably an issue with your tag configuration (references to the other tags, return type that wasn’t expected, …)

---

<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:** [May 23, 2019, 5:27pm UTC](https://forum.inductiveautomation.com/t/valid-sql-statements-in-ignition-jdbc/25259/5 "2019-05-23T17:27:51Z")

</div>

If `UDTDATA/AREA` and/or `UDTDATA/SITE` are strings, you would need single quotes in your SQL.

---

<div class="post-metadata">

**Author:** ![Esn](https://avatars.discourse-cdn.com/v4/letter/e/91b2a8/32.png) [@Esn](https://forum.inductiveautomation.com/u/Esn)\
**Post date:** [May 24, 2019, 9:17am UTC](https://forum.inductiveautomation.com/t/valid-sql-statements-in-ignition-jdbc/25259/6 "2019-05-24T09:17:50Z")

</div>

@pturmel  
That worked! Thanks Phil 👍