Convert Oracle Views to MS SQL

[Oracle to SQL Server Converter] [FAQ] [Tutorial] [Database Migration Service]

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.

Function TO_DATE($date, 'YYYYMMDD') that converts string to datetime must be replaced by CONVERT(DATETIME, $date)

Oracle function EMPTY_BLOB that creates an empty BLOB values is convered into MS SQL format like this

Function EMPTY_CLOB that creates an empty CLOB or NCLOB values must be replaced by emty string '' in SQL Server

Oracle's CONCAT($s1, $s2) function returns $s2 appended or concatenated to $s1. In SQL Server all CONCAT calls must be replaced by the plus sign '+' as a string concatenation operator. For example, CONCAT($s1, $s2) must be converted into $s1 + $s2

Function ADD_MONTHS($datetime, $n) that adds $n months to $datetime must be replaced by DATEADD($month, $n, datetime)

Function NVL($var, $expr) that returns $expr if $var is NULL must be replaced by ISNULL($var, $expr), while 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
```
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
```

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
```

Oracle function MOD that returns the remainder of division of one number by another is converted into MS SQL operator %

Function SYSDATE that returns the current date and time is converted into GETDATE()

Oracle function TRUNC for datetime is convered into MS SQL format like this

Unlike Oracle, Microsoft SQL requires aliases for subqueries. For example FROM (SELECT ...) must be modified as FROM (SELECT ...) T

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 MS 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
```

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 this way, function LPAD($val, $n_symbols, $symbol) must be replace by the expression below:
```
left($val + replicate ($symbol,$n_symbols), $n_symbols)
```

Oracle function SUBSTR is convered into MS SQL format like this

Specialists of Intelligent Converters offer Database Migration Service to help you migrate SQL and PL/SQL from Oracle to SQL Server or Azure SQL.