Is there limited functionality writing SQL queries using CTE's? Whenever I use CTE's, Ignition thinks I am trying to run an update statement, when in actuality I am trying to access data in my CTE. Thanks!
Some parts of Ignition (like the designer's query browser) have to guess what kind of statement you've given it. The guess is super-lame--I think it just looks for "SELECT" before other keywords. No help for that in the query browser. Most other parts of Ignition the type of query is implied by the function name or is settable in the local properties (named queries, query tags).
Thanks for the quick response, I was using the query browser! Do you think running a query with CTE using system.db.runQuery in a script would be fine? My goal is to populate a dataset tag with the results of my query based on a user entered ID.
Yes, should. You can try that in the Designer's script console. Place the SQL in a triple-quoted jython string block to keep your formatting.