Hybrid use of Decode functions in Oracle with ABS, sign, TRUNC, substr functions

The decode () function is one of the powerful functions of Oracle Pl/sql, and it is currently available only in Oracle company SQL, and not in the SQL implementations of other database vendors. (But it's not a standard SQL function, but that's his advantage, and other databases don't provide such a powerful function.) ）

Function:

1, flow control, the equivalent of If-then-else function.

Usage is as follows: Decode (expression, search, result [, search, result] ... [, default])

Expression The expression you want to compare, search for the field you want to compare with expression ...

Results if expression is the same as search, return the result ...

Default this parameter is optional if no search is matched to the expression. Returns this result if this parameter is not set, NULL is returned when there is no search on expression match.

Search and result can appear on multiple occasions, representing a variety of situations to match.

Suitable for 9i or above, Oracle 9i, Oracle 10g, Oracle 11g

Example: Select supplier_name,decode (supplier_id,1000, ' IBM ', 10001, ' Microsoft ', ' 1002 ', ' Hewlett Packard ', ' Gateway ') result from suppliers;

It is equivalent to:

IF supplier_id = 10000 THEN

Result: = ' IBM ';

elsif supplier_id = 10001 THEN

Result: = ' Microsoft ';

elsif supplier_id = 10002 THEN

Result: = ' Hewlett Packard ';

ELSE

Result: = ' Gateway ';

End IF;

Another example, and the order by a piece used, put in the back.

SELECT * from Table_subject ORDER by decode (subject_name, ' language ', 1, ' Math ', 2,, ' Foreign Language ', 3)

2, compare the size, at this time often with sign, trunc two functions with the use.

A, with sign use

Select decode (sign (variable 1-variable 2),-1, variable 1, variable 2) from dual; --Take a smaller value
The sign () function returns 0, 1, and 1, depending on whether a value is 0, positive, or negative.

If Date1 > Date2, the Decode function returns DATE2. Otherwise the Decode function returns DATE1.

Decode (DATE1-DATE2)-ABS (DATE1-DATE2), 0, Date2, date1)

ABS function to obtain absolute value, if DATE1-DATE2 is greater than 0, positive, assuming the result is 2, ABS (DATE1-DATE2) is positive, take 2. The difference between the two is 0, return 0, take the value of a small return date, Date2, and vice versa. Take Data1

If you match the sign function, the expression is much simpler:

DECODE (SIGN (Date1-date2), 1, Date2, date1)

Another example:

The Sign/decode Union is useful for comparisons of figures such as sales bonuses.

DECODE (SIGN (Actual-target),-1, ' NO Bonus for You ', 0, ' Just made it ', 1, ' Congrats, you are a winner ')

Suppose we want to raise wages for the star staff, the standard is: the wages of 8000 yuan will be added 20%; wages at 8000 yuan plus 15%

Select Decode (sign (salary-8000), 1,salary*1.15,-1,salary*1.2,salary from employee

b, with trunc to seek the scope

Although you cannot use the Decode function to determine the range of numbers. But we can try to create an expression that can get a range of numbers, the next number corresponds to the next specified range, and so on.

I want to write a decode function that requires the following:

If Yrs_of_service < 1 returns 0.04

If Yrs_of_service >= 1 and < 5 returns 0.04

If Yrs_of_service > 5 returns 0.06

The expression can be written as follows:

Select Emp_name,decode (trunc (yrs_of_service+3)/4), 0,0.04,1,0.04,0.06) as Perc_value from employees;

Note: The maximum number of parameters for the decode function is 255, including expression, search, and result. Exceeding this range will be reported as "ora-00939:too many arguments for function". Error

3, the transformation of table and view structure

There is an existing sales table sale, the table structure is:

Month char (6)--month

Sell number (10,2)--monthly sales Amount

The existing data is:

200001 1000

200002 1100

200003 1200

200004 1300

200005 1400

200006 1500

200007 1600

200101 1100

200202 1200

200301 1300

Data that you want to translate into the following structure:

Year char (4)--years

Month1 Number (10,2)--January Sales Amount

Month2 Number (10,2)--February Sales Amount

Month3 Number (10,2)--March Sales Amount

Month4 Number (10,2)--April Sales Amount

Month5 Number (10,2)--May Sales Amount

Month6 Number (10,2)--June Sales Amount

Month7 Number (10,2)--July Sales Amount

Month8 Number (10,2)--August Sales Amount

Month9 Number (10,2)--September Sales Amount

Month10 Number (10,2)--October Sales Amount

Month11 Number (10,2)--November Sales Amount

Month12 Number (10,2)--December Sales Amount

The SQL statement for structural transformation is:

Create or Replace view

V_sale (YEAR,MONTH1,MONTH2,MONTH3,MONTH4,MONTH5,MONTH6,MONTH7,MONTH8,MONTH9,MONTH10,MONTH11,MONTH12)

As

Select

SUBSTRB (month,1,4),

Sum (SUBSTRB (month,5,2), ' decode ', sell,0)),

Sum (SUBSTRB (month,5,2), ' decode ', sell,0)),

Sum (SUBSTRB (month,5,2), ' decode ', sell,0)),

Sum (SUBSTRB (month,5,2), ' decode ', sell,0)),

A function substrb is used in the example above. A function that takes part of the data of a value.

Here we will summarize the function in addition to the decode:

ABS mathematical function, to find absolute value, this is nothing to say.

Sign is a symbolic function, sign (x), where x can be a value, or it can be an expression that returns a value.

The sign () function returns 0, 1, and 1, depending on whether a value is 0, positive, or negative.

Trunc intercept function, mainly used for the interception of date or value, here are two examples, easy to understand:

TRUNC (To_date (' 24-nov-1999 08:00 pm '), ' dd-mon-yyyy hh:mi am ') = ' 24-nov-1999 12:00:00 am ' TRUNC (to_date (' 24-nov-1999 08: 　　PM ', ' dd-mon-yyyy hh:mi am '), ' hh ' = ' 24-nov-1999 08:00:00 am ' trunc (sysdate, ' yyyy ')--return to the first day of the year. 　　Trunc (sysdate, ' mm ')--return to the first day of the month. 　　Trunc (sysdate, ' d ')--Returns the first day of the current week. Trunc (sysdate, ' DD ')--return to current month day

TRUNC (Number[,decimals]) of which: number the value to be intercepted decimals indicate the digits after which the decimal point needs to be preserved. Optional, ignore it to truncate all decimal parts The following is the use of this function: TRUNC (89.985,2) =89.98 TRUNC (89.985) =89 TRUNC (89.985,-1) =80

Note: The second argument can be a negative number, represented as a portion of the following part of the specified digit to the left of the decimal point, that is, 0. Similar to rounding, for example, 1 is the integer to the very bit, if it is 1, it is rounded to 10 digits, and so on.

The substr substr () function returns part of the string. Syntax: substr (string,start,length) required. 　　Specify where to start the string. Positive number-starting at the specified position of the string minus 0-starting at the specified position at the end of the string-at the first character in the string