ASA SQL User's Guide
  Transact-SQL Compatibility
    Writing compatible SQL statements

Writing compatible queries

There are two criteria for writing a query that runs on both Adaptive Server 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 Adaptive Server Anywhere setting.

Adaptive Server 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 T_string

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

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

For a full description of the SELECT statement, see SELECT statement.

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

• SHARED keyword

• COMPUTE clause

• FOR BROWSE clause

• GROUP BY ALL clause

Notes 

• The INTO table_name clause, which creates a new table based on the SELECT statement result set, is supported only for declared temporary tables where the table name starts with a #. Declared temporary tables exist for a single connection only.

• Adaptive Server 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 FOR READ ONLY clause and the FOR UPDATE clause are parsed, but have no effect.

• The performance parameters part of the table specification is parsed, but has no effect.

• The HOLDLOCK keyword is supported by Adaptive Server 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 carried out 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 Adaptive Server Anywhere, but has no effect.

• Transact-SQL uses the SELECT statement to assign values to local variables:

  SELECT @localvar = 42

  The corresponding statement in Adaptive Server Anywhere is the SET statement:

  SET localvar = 42

  The variable name can optionally be set using the SET statement and the Transact-SQL convention of an @ sign preceding the name:

  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.