Select * from dual

T-SQL is the language engine of SQL Server, while Oracle's language engine is PLSQL. Both query languages have extended the ANSI SQL-92 standard to provide additional support. Almost all applications you create use these complementary features. This article describes the most common and non-standard Oracle extensions. It also describes how to 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. There is a special table dual in the Oracle database. A dual table is an existing table in Oracle and can be read by any user. It is often used in select statements without a target table. The dual table is created by Oracle along 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 varchar2 (1) type and has a row value of 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'

The following are null-related knowledge and dual is used:
SQL> select 1 from dual where null = NULL;
No records found
SQL> select 1 from dual where null = '';
No records found
SQL> select 1 from dual where ''= '';
No records found
SQL> select 1 from dual where NULL is null;
1
---------
1
SQL> select 1 from dual where nvl (null, 0) = nvl (null, 0 );
1
---------
1
View connected users
SQL> Select User from dual;

View current date and time
SQL> select sysdate from dual;

Connection

Oracle uses the | symbol 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.

You can use round or floor in SQL Server.

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

Digital Conversion

Oracle's to_char function can convert the n-bit number data type to the varchar2 data type and use an optional numeric format.

SQL Server Returns the character data after numeric conversion through the STR function. However, this function does not have a convenient format parameter.

Oracle query:
Select to_char (123.45, 99999999999999) from Tab
Select to_char (expiry_date, 'ddmonyyyy') from Tab

The following are SQL Server version queries:
Select STR (123.45, 14)
Select STR (round (123.455, 2)
Select cast (replace (convert (varchar (12), expirydate, 106), '','') as varchar (9 ))

Length and Len

Oracle queries are as follows:
Select length ('sqlmag') "Length in characters" from dual;

The preceding query is written in the following way in SQL Server:
Select Len ('sqlmag') "Length in characters"

Date

Both systems have their own current date and time formats.

The date obtained by Oracle is as follows:
Sysdate

SQL Server is like this:
Getdate ()

You can use various syntaxes to operate dates. The following code adjusts the format of the month in the Oracle date value (return date plus N months ):
Select add_months (sysdate, 12) from dual

SQL Server performs the same functions as follows:
Select dateadd (mm, 12, getdate ())

Data Subtraction is also different. The following code directly deletes data in Oracle:
Select sysdate-add_months (sysdate, 12) from dual

SQL Server does this:
Select datediff (DD, getdate (), dateadd (mm, 12, getdate ()))

Oracle changes the current Sequence Value

SQL> select seq. nextval from dual;
Nextval
----------
21
SQL> alter sequence seq increment by 79;
The sequence has been changed.

SQL> select seq. nextval from dual;
Nextval
----------
100
SQL> alter sequence seq increment by 1;
The sequence has been changed.

SQL> select seq. nextval from dual;
Nextval
----------
101
SQL>
__________________
The sequence does not seem to be able to modify the current value.

Round Function Description:

The round function is a rounding function that returns a number expression and rounds it to a specified length or precision.

Syntax:
Round (numeric_expression, length [, function])
Parameters:
Numeric_expression
Expression of the exact or approximate numeric data type category (except for the BIT data type ).
Length
The precision of numeric_expression to be rounded. The length must be tinyint, smallint, or Int. When length is a positive number, numeric_expression is rounded to the specified decimal place. When length is a negative number, numeric_expression is rounded to the left of the decimal point specified by length.

Function
Is the operation type to be executed. The function must be tinyint, smallint, or Int. If the value of function or function is omitted is 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

Return type
Returns the same type as numeric_expression.
Note
Round always returns a value. If length is a negative number and the number is greater than the number before the decimal point, round returns 0

Example
Result
Remark

Select round (748.58,-1)
750.00
Rounded to the left 1st digits of the decimal point. The other digits are 0.

Select round (748.58,-2)
700.00
Rounded to the left 2nd digits of the decimal point. The other digits are 0.

Select round (748.58,-3)
1000.00
Rounded to the left 3rd digits of the decimal point. The other digits are 0.

Select round (748.58,-4)
0
Length is a negative number and greater than the number before the decimal point. Round returns 0

Select round (748.58, 1)
748.60
Number of decimal places specified by length

Select round (748.58, 2)
748.58
Number of decimal places specified by length

Select round (124.9994, 3)
124.9990
Number of decimal places specified by length

Select round (124.9995, 3)
125.0000
Number of decimal places specified by length

Select round (150.75, 0)
151.00
Rounding results

Select round (150.75)
150.00
Truncation result

From: http://blog.csdn.net/wngvo/archive/2007/09/18/1789205.aspx