DB Best blog

In spite of the fact that Sybase Adaptive Server Anywhere and Sybase Adaptive Server Enterprise have many common features, the conversion of Sybase ASA to SQL Server 2005 can cause more problems than the conversion of Sybase ASE database to SQL Server. It depends on differences in SQL dialects, which SQL Sybase ASE and Sybase ASA use.

While working with last versions of Sybase ASA, you can get the impression of double standards in writing queries. The fact of the matter is that the syntax used in previous versions of Adaptive Server Anywhere is supported for some operators, especially in procedural language which is used for writing procedures, triggers and packages. Adaptive Server Anywhere supports two dialects for such operators. These dialects are called Transact SQL for SQL Sybase ASE and Watcom SQL for Sybase ASA. If queries are written by means of Transact SQL dialect then the conversion of such queries will not differ from the conversion of Sybase ASE queries.

For all that, what you will face with if you need to convert a SELECT expression from SQL Anywhere to SQL Server.  Let us consider some simple constructions.

We will begin with the simplest case: to output information from a database, for example, about a current user.

SQL Anywhere:
select user from dummy
or
select user

SQL Server:
select user

Next task is to output the first row from a query. This can be done in two ways: by using SELECT FIRST construction or SELECT TOP 1. But, to output the first N rows, SELECT TOP N construction is used.

SQL Anywhere:

To output the first row:
select first <field list> from <table> order by <field list>
The equivalent is:
select top 1 <field list> from <table> order by <field list>
Or to output the first N rows:
select top N <field list> from <table> order by <field list>

SQL Server:
To output the first row
select top 1 <field list> from <table> order by <field list>
or to output the first N rows:
select top N <field list> from <table> order by <field list>

It is important to point out that using ORDER BY construction is compulsory in SQL Anywhere but you can omit it in SQL Server.

If you want to use a conditional operator in a SELECT statement you can do this in two ways: to use either CASE expression or IF expression. By the way, the usage and the syntax of CASE expression fully coincide with SQL Server. It is important to distinguish IF expression from IF statement because it is forbidden to use IF statement in SELECT operator. The difference is that IF expression cannot contain more than one conditional check, but IF statement can.

It is better to use CASE expression instead of IF statement.

SQL Anywhere:

SELECT ID,
   ( IF Name = ‘Tee Shirt’ then ‘Shirt’
      ELSE ‘Unknown’
   ENDIF ) as Type
FROM Products

SQL Server:

SELECT ID,
   ( CASE Name
      WHEN ‘Tee Shirt’ then ‘Shirt’
      ELSE ‘Unknown’
   END ) as Type
FROM Products

But this is not all possible variants of CASE expression usage while converting SELECT expressions to SQL Server. There is IFNULL() function in SQL Anywhere. Its syntax is:

IFNULL( expression-1, expression-2 [ , expression-3 ] ).
If you use IFNULL function with two parameters, for example like this:

SELECT IFNULL(Weight, 50)
FROM Production.Product

then you can just replace it with ISNULL function in SQL Server:

SELECT AVG(ISNULL(Weight, 50))
FROM Production.Product

But if you need to convert an expression like

SELECT IFNULL(Weight, 50, 100)
FROM Production.Product

then you cannot avoid using CASE expression:

SELECT (case Weight
         when null then 50
         else 100
         end
        )
FROM Production.Product

We often need to know the current date and time. You can do this by several means in SQL Anywhere. The functions NOW(), GETDATE() and special variables CURRENT TIMESTAMP and CURRENT_TIMESTAMP return the same values and fully equivalent to GETDATE() and CURRENT_TIMESTAMP in SQL Server.

To get the current date and time is simple thing: all works quite equally both in SQL Anywhere and in SQL Server. But date and time processing is much more difficult as SQL Anywhere supports DATE, DATETIME, SMALLDATETIME, TIME, TIMESTAMP in contrast to SQL Server 2005 which has only datetime and smalldatetime data types to store date and time values.

Both the platforms contain different functions to work with date. I will just demonstrate here what to do if your SQL Anywhere query use DATEFORMAT() or DATE() function and you have to get the same result in SQL Server.

To cast a date value to some format there is CONVERT function in SQL Server. That means that if you execute SQL Anywhere query

SELECT DATEFORMAT( <expression>,’YYYY-MM-DD’ )

or

SELECT DATEFORMAT(<expression>,’DD/MM/YYYY’)
 
to get the same result in SQL Server, you should execute the query

SELECT SUBSTRING(CONVERT(CHAR,<expression>,20), 1, 10)

or

SELECT CONVERT(CHAR,<expression>,103)

An equivalent for the query

SELECT DATE( expression )

can be

SELECT SUBSTRING(CONVERT(CHAR,CAST( <expression>  as datetime),20), 1, 10)

However, if your application in Sybase ASA database is written using Transact SQL dialect then the conversion of database objects can be simplified as there are special applications for this purpose, for example Microsoft SQL Server Migration Assistant for Sybase:

     http://www.microsoft.com/sqlserver/2005/en/us/migration-sybase.aspx

Written by Yuri Kovtun

Tagged as ASA, Conversion, Sybase + Categorized as Newsletters, Newsletter #2, December 2, 2008