How are SQL Queries handled

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.

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.

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

What if you try this:

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}

Same error as before.

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.