Writing compatible queries

There are two criteria for writing a query that runs on both SQL Anywhere and Adaptive Server Enterprise databases:

The data types, expressions, and search conditions in the query must be compatible.
The syntax of the SELECT statement itself must be compatible.

This section explains compatible SELECT statement syntax, and assumes compatible data types, expressions, and search conditions. The examples assume the quoted_identifier setting is Off: the default Adaptive Server Enterprise setting, but not the default SQL Anywhere setting.

SQL Anywhere supports the following subset of the Transact-SQL SELECT statement:

Syntax

SELECT [ ALL | DISTINCT ] select-list
...[ INTO #temporary-table-name ]
...[ FROM table-spec [ HOLDLOCK | NOHOLDLOCK ],
...  table-spec [ HOLDLOCK | NOHOLDLOCK ], ... ]
...[ WHERE search-condition ]
...[ GROUP BY column-name, ... ]
...[ HAVING search-condition ]
   [ ORDER BY { expression | integer }
      [ ASC | DESC ], ... ]

Parameters

select-list:
 table-name.*
|  *
| expression
| alias-name = expression
| expression as identifier  
| expression as string

table-spec:
[ owner . ]table-name
...[ [ AS ] correlation-name ]
 ...[ ( INDEX index_name [ PREFETCH size  ][ LRU | MRU ] ) ]

alias-name:
identifier | 'string' | "string"

SQL Anywhere does not support the following keywords and clauses of the Transact-SQL SELECT statement syntax:

SHARED keyword
COMPUTE clause
FOR BROWSE clause
FOR UPDATE clause
GROUP BY ALL clause

Notes

SQL Anywhere does not support the Transact-SQL extension to the GROUP BY clause allowing references to columns and expressions that are not used for creating groups. In Adaptive Server Enterprise, this extension produces summary reports.
The performance parameters part of the table specification is parsed, but has no effect.
The HOLDLOCK keyword is supported by SQL Anywhere. It makes a shared lock on a specified table or view more restrictive by holding it until the completion of a transaction (instead of releasing the shared lock as soon as the required data page is no longer needed, whether or not the transaction has been completed). For the purposes of the table for which the HOLDLOCK is specified, the query is performed at isolation level 3.
The HOLDLOCK option applies only to the table or view for which it is specified, and only for the duration of the transaction defined by the statement in which it is used. Setting the isolation level to 3 applies a holdlock for each select within a transaction. You cannot specify both a HOLDLOCK and NOHOLDLOCK option in a query.
The NOHOLDLOCK keyword is recognized by SQL Anywhere, but has no effect.
Transact-SQL uses the SELECT statement to assign values to local variables:
SELECT @localvar = 42;
The corresponding statement in SQL Anywhere is the SET statement:
SET @localvar = 42;
Adaptive Server Enterprise does not support the following clauses of the SELECT statement syntax:
- INTO host-variable-list
- INTO variable-list
- Parenthesized queries
Adaptive Server Enterprise uses join operators in the WHERE clause, rather than the FROM clause and the ON condition for joins.