User's Guide
   PART 5. The Adaptive Server Family
     CHAPTER 30. Transact-SQL Compatibility       

Writing compatible SQL statements

This section describes some issues of compatibility between Adaptive Server Enterprise and Adaptive Server Anywhere at the SQL statement level.

  Creating compatible tables

Adaptive Server Anywhere does not support named constraints or named defaults, but does support user-defined data types which allow constraint and default definitions to be encapsulated in the data type definition. It also supports explicit defaults and CHECK conditions in the CREATE TABLE statement.

Temporary tables 

You can create a temporary table by preceding the table name in a CREATE TABLE statement with a pound sign (#). These temporary tables are Adaptive Server Anywhere declared temporary tables, and are available only in the current connection. For information about declared temporary tables in Adaptive Server Anywhere, see DECLARE LOCAL TEMPORARY TABLE statement.

By default, columns in Adaptive Server Enterprise default to NOT NULL, whereas in Adaptive Server Anywhere the default setting is NULL, to allow NULL values. This setting can be controlled using the allow_nulls_by_default option. You should explicitly specify NULL or NOT NULL to make your data definition statements transferable.

     For information on this option, see Setting options for Transact-SQL compatibility.

Physical placement of a table is carried out differently in Adaptive Server Enterprise and in Adaptive Server Anywhere. The ON segment-name clause is supported in Adaptive Server Anywhere, but segment-name refers to an Adaptive Server Anywhere dbspace.

     For information about the CREATE TABLE statement, see CREATE TABLE statement.

  Writing compatible queries

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

• Ensure that the data types, expressions, and search conditions in the query are compatible.

• Ensure that the syntax of the SELECT statement itself is compatible.

This section is concerned with compatible SELECT statement syntax, and assumes compatible data types, expressions, and search conditions. The examples assume that the QUOTED_IDENTIFIER setting is OFF: the default Adaptive Server Enterprise setting, but not the default Adaptive Server Anywhere setting.

The following subset of the Transact-SQL SELECT statement is supported in Adaptive Server Anywhere.

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 [ ASC | DESC ], ... ] |
| [ ORDER BY integer [ ASC | DESC ], ... ] |

Parameters 

select-list:
{ table-name | alias-name = expression }...

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.

The following keywords and clauses of the Transact-SQL SELECT statement syntax are not supported by Adaptive Server Anywhere:

• The SHARED keyword.

• The COMPUTE clause.

• The FOR BROWSE clause.

• The 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

  However, using the Transact-SQL SELECT to assign values to variables is supported inside batches.

• The following clauses of the SELECT statement syntax are not supported by Adaptive Server Enterprise:

  • INTO host-variable-list

  • INTO variable-list.

  • Parenthesized queries.

• Adaptive Server Enterprise does not support the use of the FROM clause and the ON condition for joins. Instead, it uses join operators in the WHERE clause.

  Compatibility of joins

In Transact-SQL, joins are specified in the WHERE clause, using the following syntax:

Start of select, update, insert, delete, or subquery
FROM {table-list | view-list} WHERE [ NOT ]
...[ table-name.| view name.]column-name
join-operator
...[ table-name.| view-name.]column_name
...[ { AND | OR } [ NOT ]
... [ table-name.| view-name.]column_name
join-operator
[ table-name.| view-name.]column-name
]...
end of select, update, insert, delete, or subquery

The join_operator in the WHERE clause may be any of the comparison operators, or may be either of the following outer-join operators:

• *=     Left outer join operator

• =*     Right outer join operator.

The Transact-SQL outer-join operators are supported in Adaptive Server Anywhere as an alternative to the native SQL/92 syntax. You cannot mix dialects within a query. This rule applies also to views used by a query(an outer-join query on a view must follow the dialect used by the view-defining query.

Adaptive Server Anywhere also provides a SQL/92 syntax for joins other than outer joins, in which the joins are placed in the FROM clause rather than the WHERE clause.

     For information about joins in Adaptive Server Anywhere and in SQL/92, see FROM clause.

     For more information on Transact-SQL compatibility of joins, see Transact-SQL outer joins.