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 |
(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. |
Comments
0 comments
Please sign in to leave a comment.