# How are SQL Queries handled

**URL:** <https://forum.inductiveautomation.com/t/how-are-sql-queries-handled/9598>\
**Category:** Ignition\
**Created:** [January 23, 2015, 9:24pm UTC](https://forum.inductiveautomation.com/t/how-are-sql-queries-handled/9598 "2015-01-23T21:24:26Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![CPowell](https://avatars.discourse-cdn.com/v4/letter/c/a183cd/32.png) [@CPowell](https://forum.inductiveautomation.com/u/CPowell)\
**Post date:** [January 23, 2015, 9:24pm UTC](https://forum.inductiveautomation.com/t/how-are-sql-queries-handled/9598/1 "2015-01-23T21:24:26Z")

</div>

I have several tables in a database, but a few databases do not have the Column ‘Prompt’.  
I did not want to return all the columns, so I added the following code to check if the column existed and if it does ask for it, otherwise don’t.

```auto
IF (SELECT COUNT(*) FROM information_schema.columns WHERE TABLE_NAME = '{Root Container.DataTable}' and column_name = 'Prompt') != 0
	SELECT ndx, Prompt, Comment FROM {Root Container.DataTable}
ELSE
	SELECT ndx, Comment FROM {Root Container.DataTable}
```

However, I get the following error:

Exception: Error running query:  
On: Prompts Setup.Root Container.Power Table 1.data  
caused by GatewayException: Invalid column name ‘Prompt’.  
caused by SQLServerException: Invalid column name ‘Prompt’.

Is it running both queries and ignoring the IF statement…

If I modify the code to remove the Prompt and Comment columns so I can see different results. It works.

```auto
IF (SELECT COUNT(*) FROM information_schema.columns WHERE TABLE_NAME = '{Root Container.DataTable}' and column_name = 'Prompt') != 0
	SELECT ndx FROM {Root Container.DataTable}
ELSE
	SELECT ndx, Comment FROM {Root Container.DataTable}
```

Ignition v7.7.1 (b2014092209)  
Java: Oracle Corporation 1.7.0\_45

I have tried running the code on the SQL server with no error.  
Any suggestions?

Cheers,  
Chris

---

<div class="post-metadata">

**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\
**Post date:** [January 23, 2015, 10:22pm UTC](https://forum.inductiveautomation.com/t/how-are-sql-queries-handled/9598/2 "2015-01-23T22:22:13Z")

</div>

What if you try this:

```auto
IF (SELECT COUNT(*) FROM information_schema.columns WHERE TABLE_NAME = '{Root Container.DataTable}' and column_name = 'Prompt') != 0
   SELECT ndx, [Prompt], Comment FROM {Root Container.DataTable}
ELSE
   SELECT ndx, Comment FROM {Root Container.DataTable}
```

---

<div class="post-metadata">

**Author:** ![CPowell](https://avatars.discourse-cdn.com/v4/letter/c/a183cd/32.png) [@CPowell](https://forum.inductiveautomation.com/u/CPowell)\
**Post date:** [January 23, 2015, 10:33pm UTC](https://forum.inductiveautomation.com/t/how-are-sql-queries-handled/9598/3 "2015-01-23T22:33:58Z")

</div>

Same error as before.

---

<div class="post-metadata">

**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\
**Post date:** [January 24, 2015, 12:21am UTC](https://forum.inductiveautomation.com/t/how-are-sql-queries-handled/9598/4 "2015-01-24T00:21:51Z")

</div>

The Microsoft SQL Server JDBC Driver used by Ignition may not be able to handle this query. There are some ways to work around this.

You could put this query into a stored procedure and then call the stored procedure from Ignition.

Or you could break up this query into two separate queries and run them using Python.