Turn, Oracle--decode function

Decode () function Introduction: main role: Translating query results to other values

How to use:

Select decode (ColumnName, value 1, translation value 1, value 2, translation value 2,... Value n, translation value n, default value)

From Talbename

Where ...

Where ColumnName is the column defined in the table to be selected,

• Explanation of Meaning:

Decode (condition, value 1, translation value 1, value 2, translation value 2,... The value n, the translation value n, the default value) is understood as follows:

if (condition = = value 1)

Then

return (translation value 1)

elsif (Condition = = value 2)

Then

Return (translation value 2)

......

elsif (Condition = = value N)

Then

return (translated value n)

Else

return (default value)

End If

Note: The default value can be either the column name you want to choose, or the other values you want to define, such as other, or omitted;

Example:

Displays the employee's name, age salary (with new salary as the column header). The method of calculation of the age wage: 10 years from the date of employment, the wage increase of 10%, 20 years, wages increased by 20%, full 30 years, wages increased by 30%;

  Select ename,decode (trunc (Months_between (sysdate,hiredate)/0),1 , Sal*1.1,2, Sal*1.2,3, Sal*1.3   as "New Salary"  from EMP;

Add:

Calculate the number of days, months, and years of two date intervals in Oracle:

One, the number of days:

In Oracle, the number of days can be obtained by subtracting two dates directly;

Select sysdate-to_date ('2017-10-15','yyyy-mm-dd '  from dual; return Result: 1.6... (plus hours)

Second, the number of months:

Calculate the number of months, need to use the Months_between function;

1 --Months_between (Date1,date2)2 --if the day of the two dates is the same, or the last day of the month, the returned result is an integer. Otherwise, the returned result will contain a fractal part (calculated in 31 days for January)3 SelectMonths_between (To_date ('02/28/2015','mm/dd/yyyy'), To_date ('12/31/2014','mm/dd/yyyy')) "Moths" fromdual;4return Result:25 SelectMonths_between (To_date ('01/01/2015','mm/dd/yyyy'), To_date ('12/31/2014','mm/dd/yyyy')) "MONTHS" fromDUAL; 6return Result:0.032258064516129

Third, year number:

The number of years is calculated by calculating the number of moon and then dividing by 12;

Select Trunc (Months_between (to_date ('08/06/2015','mm/dd/yyyy'), to_date ('08/06/ 'mm/dd/yyyy ')/from dual;  3--return Result:2               

Turn, Oracle--decode function