Data Manipulation Language

Query requirements differ between Adaptive Server Enterprise, SQL Anywhere, and Sybase IQ.

• General Guidelines for Writing Portable SQL
  Even if more than one server supports a given SQL statement, it might be a mistake to assume that default behavior is the same on each system.
• Criteria for Writing Compatible Queries
  There are two criteria for writing a query that runs on both Sybase IQ and Adaptive Server Enterprise databases.
• Subquery Support
  Sybase IQ currently provides support for subqueries that is somewhat different from that provided by Adaptive Server Enterprise and SQL Anywhere.
• GROUP BY Clause Support
  GROUP BY ALL support differs between Adaptive Server Enterprise, SQL Anywhere, and Sybase IQ.
• COMPUTE Clause Support
  COMPUTE support differs between Adaptive Server Enterprise, SQL Anywhere, and Sybase IQ.
• WHERE Clause Support
  The WHERE clause differs between Adaptive Server Enterprise, SQL Anywhere, and Sybase IQ in support for the Contains() predicate, and treatment of trailing white space in the Like() predicate.
• Transact-SQL Outer Joins Support
  Supported syntax for outer joins differs between Adaptive Server Enterprise, SQL Anywhere, and Sybase IQ.
• ANSI Joins Support
  Support for ANSI join syntax differs between Adaptive Server Enterprise, SQL Anywhere, and Sybase IQ.
• Null Comparisons Support
  Adaptive Server Enterprise has Transact-SQL extensions that permit predicates to compare the null value.
• Zero-length Strings Support
  Zero-length strings are treated differently in Adaptive Server Enterprise, SQL Anywhere, and Sybase IQ.
• HOLDLOCK, SHARED, and FOR BROWSE Support
  HOLDLOCK, SHARED, and FOR BROWSE syntax differs between Adaptive Server Enterprise, SQL Anywhere, and Sybase IQ.
• SQL Function Support
  Sybase IQ supports most of the same functions as SQL Anywhere and Adaptive Server Enterprise, with some differences.
• OLAP Function Support
  Currently, Adaptive Server Enterprise does not support OLAP functions. Sybase IQ and SQL Anywhere do.
• System Function Support
  SQL Anywhere and SQL AnywhereSybase IQ do not support certain Adaptive Server Enterprise system functions.
• User Defined Function Support
  User-defined function (UDF) support differs between Adaptive Server Enterprise, SQL Anywhere, and Sybase IQ:
• Differences Interpreting Arithmetic Expressions on Dates
  SQL Anywhere and Sybase IQ interpret arithmetic expressions on dates as shorthand notation for various date functions. Adaptive Server Enterprise does not.
• SELECT INTO Statement Support
  There are differences in the types of tables permitted in SELECT INTO statements in Adaptive Server Enterprise, SQL Anywhere, and Sybase IQ.
• Updatable Views Support
  Adaptive Server Enterprise and SQL Anywhere are more liberal than ANSI permits on the view definitions that are updatable when the WITH CHECK option is not requested.
• Support for FROM Clause in UPDATE and DELETE
  Sybase IQAdaptive Server Enterprise, SQL Anywhere, and Sybase IQ all support the FROM clause with multiple tables in UPDATE and DELETE.