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