In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.
The syntax for the decode function is:
decode( expression , search , result [, search , result]... [, default] )expression is the value to compare.
search is the value that is compared against expression.
result is the value returned, if expression is equal to search.
default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).
Applies To:
- Oracle 9i, Oracle 10g, Oracle 11g
For example:
You could use the decode function in an SQL statement as follows:
SELECT supplier_name, decode(supplier_id, 10000, 'IBM', 10001, 'Microsoft', 10002, 'Hewlett Packard', 'Gateway') result FROM suppliers;
The above decode statement is equivalent to the following IF-THEN-ELSE statement:
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;
The decode function will compare each supplier_id value, one by one.
Frequently Asked Questions
Question: One of our viewers wanted to know how to use the decode function to compare two dates (ie: date1 and date2), where if date1 > date2, the decode function should return date2. Otherwise, the decode function should return date1.
Answer: To accomplish this, use the decode function as follows:
decode((date1 - date2) - abs(date1 - date2), 0, date2, date1)The formula below would equal 0, if date1 is greater than date2:
(date1 - date2) - abs(date1 - date2)Helpful Tip: One of our viewers suggested combining the SIGN function with the DECODE function as follows:
The date example above could be modified as follows:
DECODE(SIGN(date1-date2), 1, date2, date1)The SIGN/DECODE combination is also helpful for numeric comparisons e.g. Sales Bonuses
DECODE(SIGN(actual-target), -1, 'NO Bonus for you', 0,'Just made it', 1, 'Congrats, you are a winner')
Question: I would like to know if it's possible to use decode for ranges of numbers, ie 1-10 = 'category 1', 11-20 = 'category 2', rather than having to individually decode each number.
Answer: Unfortunately, you can not use the decode for ranges of numbers. However, you can try to create a formula that will evaluate to one number for a given range, and another number for the next range, and so on.
For example:
In this example, based on the formula:
SELECT supplier_id, decode(trunc ((supplier_id - 1) / 10), 0, 'category 1', 1, 'category 2', 2, 'category 3', 'unknown') result FROM suppliers;
trunc ((supplier_id - 1) / 10The formula will evaluate to 0, if the supplier_id is between 1 and 10.
The formula will evaluate to 1, if the supplier_id is between 11 and 20.
The formula will evaluate to 2, if the supplier_id is between 21 and 30.
and so on...
Question: I need to write a decode statement that will return the following:
If yrs_of_service < 1 then return 0.04How can I do this?
If yrs_of_service >= 1 and < 5 then return 0.04
If yrs_of_service > 5 then return 0.06
Answer: You will need to create a formula that will evaluate to a single number for each one of your ranges.
For example:
SELECT emp_name, decode(trunc (( yrs_of_service + 3) / 4), 0, 0.04, 1, 0.04, 0.06) as perc_value FROM employees;
Question: Is there a limit to the number of arguments that you can have in one DECODE statement? I'm getting an error, "ORA-00939: too many arguments for function".
Answer: Yes, the maximum number of components that you can have in a decode function is 255. This includes the
expression, search, and result arguments.
podia colocar os créditos de onde você copiou isso pelo menos...
ResponderExcluirSim, eu posso. Geralmente eu coloco quando copio algo interessante de outro site ou quando alguém me envia algum post, infelizmente neste caso eu esqueci e não lembro de onde eu copiei, se você sabe o link original você pode me informar para eu postar? Obrigado e desculpe pela falha.
ResponderExcluirAcho que foi daqui que o código foi copiado, estou alterando o post colocando no final dele a fonte da informação.
ResponderExcluirhttp://www.techonthenet.com/oracle/functions/decode.php