Data Manipulation Language

Query requirements differ between Adaptive Server Enterprise, SAP SQL Anywhere, and SAP 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 SAP Sybase IQ and Adaptive Server Enterprise databases.
• Subquery Support
  SAP Sybase IQ currently provides support for subqueries that is somewhat different from that provided by Adaptive Server Enterprise and SAP SQL Anywhere.
• GROUP BY Clause Support
  GROUP BY ALL support differs between Adaptive Server Enterprise, SAP SQL Anywhere, and SAP Sybase IQ.
• COMPUTE Clause Support
  COMPUTE support differs between Adaptive Server Enterprise, SAP SQL Anywhere, and SAP Sybase IQ.
• WHERE Clause Support
  The WHERE clause differs between Adaptive Server Enterprise, SAP SQL Anywhere, and SAP 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, SAP SQL Anywhere, and SAP Sybase IQ.
• ANSI Joins Support
  Support for ANSI join syntax differs between Adaptive Server Enterprise, SAP SQL Anywhere, and SAP 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, SAP SQL Anywhere, and SAP Sybase IQ.
• HOLDLOCK, SHARED, and FOR BROWSE Support
  HOLDLOCK, SHARED, and FOR BROWSE syntax differs between Adaptive Server Enterprise, SAP SQL Anywhere, and SAP Sybase IQ.
• SQL Function Support
  SAP Sybase IQ supports most of the same functions as SAP SQL Anywhere and Adaptive Server Enterprise, with some differences.
• OLAP Function Support
  Currently, Adaptive Server Enterprise does not support OLAP functions. SAP Sybase IQ and SAP SQL Anywhere do.
• System Function Support
  SAP Sybase IQ and SAP SQL Anywhere do not support certain Adaptive Server Enterprise system functions.
• User-Defined Function Support
  User-defined function (UDF) support differs between Adaptive Server Enterprise, SAP SQL Anywhere, and SAP Sybase IQ:
• Differences Interpreting Arithmetic Expressions on Dates
  SAP SQL Anywhere and SAP 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, SAP SQL Anywhere, and SAP Sybase IQ.
• Updatable Views Support
  Adaptive Server Enterprise and SAP 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
  SAP Sybase IQAdaptive Server Enterprise, SAP SQL Anywhere, and SAP Sybase IQ all support the FROM clause with multiple tables in UPDATE and DELETE.