Supported and Unsupported SQL Syntax

Versions: 8.0+

Supported SQL Syntax

QuerySurge generally supports the syntax of your data technology. You are free to use both the standard and the custom syntax that your technology offers - you are not limited to ANSI SQL. In addition, assuming that your login has the proper rights, you are not limited to SELECT statements; you should be able to use the standard SQL verbs, call functions and call stored procedures. QuerySurge will try to execute your statements as you have written them.

Because QuerySurge tries to support as broad a range of databases and data stores as possible, there are a few syntaxes that some database products support that QuerySurge does not support. 

Unsupported SQL Syntax

Following is a list of syntaxes that you should not use in QuerySurge (they will likely end up generating exceptions or runtime failures) along with alternate syntaxes/workarounds that will allow you to achieve the same goals.

	Description	Unsupported Syntax	Supported Syntax	Notes
1	Queries starting with left parentheses are not supported	(SELECT colA, colB FROM table1) UNION (SELECT colAA, colBB FROM table2)	SELECT colA, colB FROM table1 UNION SELECT colAA, colBB FROM table2	Parentheses removed
2	Queries starting with left parentheses are not supported	(SELECT colA, colB FROM table1) UNION (SELECT colAA, colBB FROM table2)	SELECT * FROM (SELECT colA, colB FROM table1) UNION (SELECT colAA, colBB FROM table2	Parentheses retained and preceeded by: SELECT * FROM
3	SELECT that does not return a resultset is unsupported	SELECT col_name(s) INTO newtable FROM table1	CREATE TABLE newtable (…); INSERT INTO newtable SELECT col_name(s) FROM table1;	This usually occurs with convenience syntax to create and populate a table from an existing table
4	WITH clause	WITH mydata AS ( SELECT  column1 ,column2 FROM mytable ) SELECT  d.column1 ,d.column2 FROM mydata d ORDER BY d.column1	SELECT  d.column1 ,d.column2 FROM (SELECT  column1 ,column2 FROM mytable) d ORDER BY d.column1	Use an inline view instead of a WITH clause
5	Leading comments	-- here's a comment SELECT colA, colB FROM table 1	SELECT colA, colB FROM table 1 -- here's a comment	Leading comment lines in queries are not supported
6	Multiline Snippet in single line  comment	SELECT colA, colB FROM table1 --WHERE ${multiline-snippet}	SELECT colA, colB FROM table1 /*WHERE ${multiline-snippet}*/	Use block comments
7	Unhandled data types (GIS, XML)		CAST(my_col as varchar(64))	Cast the type to a varchar (or other supported) type
8	Multi-statement "query"	Complex SQL Scripts	n/a	Most simple SQL scripts should execute; the chances are better if the syntax is closer to common syntaxes. However, because we support many, many SQL dialects, we  may not support the execution of your complex SQL scripts.