All Forums Tools
brydgesk 6 posts Joined 08/11
18 Oct 2012
SQL Assistant Parsing Bug?

SQL Assistant normally allows you to kick off multiple queries in sequence, if each query is in the SQL window, or if you highlight the queries you'd like to run. We just ran into an issue where a query - which ran fine by itself - would prevent any other highlighted queries from being executed when it completed. If we highlighted 4 queries to run, instead of saying "Statement 1: SELECT Submitted at ...", it would just run the first query, return the results, then stop.
It happens when a column alias begins with the word "for", used in a CASE statement without parentheses. Here is a quick example:

select case when 1=1 then 'test' end former
from (select 'a' asdf
	 from (select 'asdf' asdfasd
	     from (select * from dbc.tables
			) x
		) q
	) u;

SELECT '2nd query' from dbc.tables;

If you run these 2 queries together, only the first query will return results. If you change the name of the "former" alias to "current", or some other word that does not begin with "for", this will fix the problem. Putting parentheses around the case statement will fix the problem. Putting an "AS" before the alias will fix the problem.
So it's an easy bug to get around, but it does appear to be a bug. The SQL Assistant parser is finding the "for" keyword in the "former" alias, and not parsing the rest of the queries correctly.

You must sign in to leave a comment.