How to convert Oracle queries to SQL Server

When converting an oracle query to an SQL Server, you must be careful about some issues that are not easy to notice. We know that T-SQL is from SQL Server Language The Oracle language engine is PLSQL.

ANSI SQL-92 standards are extended to provide additional support. The application you created Program Almost all of these complementary features are used. This article describes the most common and non-standard Oracle extensions.

Convert these extensions for use in the SQL server environment.

Column Selection

Use PLSQL Run When querying data, the from clause is required, which is the same as that of SQL Server. The SELECT statement must select the target data table. In the Oracle database, there is a special table dual. Dual

Oracle is created together with the data dictionary. All users can access the table with the name dual. There is only one dummy column in this table, which is defined as the varchar2 (1) type and has a row of value x.

Selecting data from the dual table is often used to calculate the constant expression through the SELECT statement. Because dual only has one row of data, the constant is returned only once.

The dual query in Oracle is as follows:

Select 'x' from dual

The equivalent SQL Server Query looks like this:

Select 'x'

Connection

Oracle uses | as the connector, while SQL server uses the plus sign: +.

The Oracle query is as follows:

Select 'name' | 'last name'

From tablename

The corresponding SQL Server query is as follows:

Select 'name' + 'last name'

　　 Number Trade-offs

Oracle Database has a trunc function, which returns n digits of the M-bit decimal number. If M is omitted, n is 0 digits. The m value can be negative, indicating that the M digit on the left of the decimal point is truncated.

In SQL Server, you can use round or floor.

Oracle query:

Select trunc (15.79, 1) "truncate" from dual;

The following are SQL Server versions of similar queries:

Select round (15.79, 0) rounded,

Round (15.79,) truncated

Select floor (round (15.79, 0 )),

Floor (round (15.79 ))

When converting an oracle query to an SQL Server, you must be careful about some issues that are not easy to notice. We know that T-SQL is the language engine of SQL Server, while Oracle's language engine is PLSQL.

ANSI SQL-92 standards are extended to provide additional support. The Application Almost all programs use these complementary features. This article describes the most common and non-standard Oracle extensions.

Convert these extensions for use in the SQL server environment.

Column Selection

When PLSQL is used to perform data queries, the from clause is required, which is the same as that of SQL Server. The SELECT statement must select the target data table. In the Oracle database, there is a special table dual. Dual

Oracle is created together with the data dictionary. All users can access the table with the name dual. There is only one dummy column in this table, which is defined as the varchar2 (1) type and has a row of value x.

Selecting data from the dual table is often used to calculate the constant expression through the SELECT statement. Because dual only has one row of data, the constant is returned only once.

The dual query in Oracle is as follows:

Select 'x' from dual

The equivalent SQL Server Query looks like this:

Select 'x'

Connection

Oracle uses | as the connector, while SQL server uses the plus sign: +.

The Oracle query is as follows:

Select 'name' | 'last name'

From tablename

The corresponding SQL Server query is as follows:

Select 'name' + 'last name'