Supported and Unsupported SQL Syntax

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
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
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
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
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
Leading comments	-- here's a comment SELECT colA, colB FROM table 1	SELECT colA, colB FROM table 1 -- here's a comment	Avoid leading comments in a query
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
Unhandled data types (GIS, XML)		CAST(my_col as varchar(64))	Cast the type to a varchar (or other supported) type
Multi-statement "query"	Complex SQL Scripts	-	Because we support many, many SQL dialects, we cannot support the execution of complex SQL scripts. Most simple SQL scripts should execute; the chances are better if the syntax is closer to common syntax.