10 tips on converting Oracle views into Microsoft SQL format

[Oracle to SQL Server Converter]  [FAQ]  [Tutorial]

The process of database migration from Oracle to Microsoft SQL includes translating Oracle views into MS SQL format. Syntax of SELECT-queries being used for views in Oracle and MS SQL is similar but not identical. Also, these two database management systems provide different sets of built-in functions. That's why it is necessary to convert each 'CREATE VIEW' statement before passing it to the destination DBMS. This article discovers 10 of most important differences between Oracle and MS SQL syntax. The target audience for this guide should have general database management knowledge and experience in composing SQL queries.

  1. Built-in function TO_DATE must be replaced by CONVERT as follows:
    TO_DATE( $date, 'YYYYMMDD' ) -> CONVERT( DATETIME, $date )
    

  2. Oracle's CONCAT($s1, $s2) function returns $s2 appended or concatenated to $s1. There is no equivalent function in SQL Server, so all CONCAT calls must be replaced by string concatenation operator, the plus sign (+): CONCAT($s1, $s2) -> $s1 + $s2
  3. Built-in function ADD_MONTHS($datetime, $n) that adds $n months to $datetime must be replaced by DATEADD($month, $n, datetime)
  4. Built-in function nvl($var, $expr) that returns $expr if $var is NULL must be replaced by ISNULL($var, $expr), while built-in function nvl2($var,$expr1,$expr2) that returns $arg2 if $var is NULL and $arg1 if $var is not NULL must be replaced as follows:
    CASE WHEN $var IS NOT NULL THEN $arg1 ELSE $arg2 END
    

  5. Built-in function DECODE($exp, $when, $then, ...) used to evaluate a condition must be replace by the following syntax construction:
    CASE $exp WHEN $when THEN $then ... END
    
  6. In Oracle there is operator (+) that is specific notation for LEFT OUTER JOIN. So, the corresponding fragments of query like:
    SELECT ...
    FROM a,b
    WHERE a.id=b.id(+)
    
    must be converted into Microsoft SQL format as follows:
    SELECT ...
    FROM a
    LEFT JOIN b ON b.id = a.id
    
  7. Built-in function SYSDATE that returns the current date and time must be replaced by GETDATE()
  8. Unlike Oracle, Microsoft SQL requires aliases for subqueries. For example
    FROM (SELECT ...)
    
    must be modified as
    FROM (SELECT ...) T
    
  9. In Oracle LISTAGG function orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column. Although Microsoft SQL does not have direct equivalent for this function, 'FOR XML PATH' can be used in order to get the same result. For example, query:
    SELECT fld1, LISTAGG (fld2, ',') WITHIN GROUP (ORDER BY fld1) AS Members
    FROM TableName 
    GROUP BY fld1;
    
    must be converted into Microsoft SQL format as follows:
    SELECT fld1, STUFF( ( SELECT ','+ fld2 FROM TableName a
    WHERE b.fld1 = a.fld1 FOR XML PATH('') ), 1, 1, '' ) Members
    FROM TableName b
    GROUP BY fld1
    
  10. Built-in function LPAD($val, $n_symbols, $symbol) padding the number ($n_symbols) of symbols ($symbol) to the string $val must be replaced by the following expression:
    right (replicate ($symbol,$n_symbols) + $val, $n_symbols)
    
    Following the same way, built-in function LPAD($val, $n_symbols, $symbol) must be replace by the expression below:
    left($val + replicate ($symbol,$n_symbols), $n_symbols)
    

More articles about Oracle, MS SQL and other databases can be found at https://www.convert-in.com/articles.htm