If expression does not match any of the search value, then the query returns the default value. The data type of the returned value is the data type of the first result value that is returned. If we don’t provide default value then query returns NULL. If expression is equal to search1 is returned, if it equal to search2 then result2 is returned and so on.ĭefault – If expression is not equal to any search value, then the query returns the default value. Result – If expression value is equal to the search value, then the DECODE function returns result. The data types of all subsequent search values are converted to the data type of first search value. Search – These are the values against which expression is compared. The data type of this value is converted to the data type of the first search value before comparing. You can also use DECODE to create columns of summarized data in a single query.Expression – This is the value that we provide to compare with other search values. You can have up to 255 elements within the parentheses. Each of the ifs and thens as well as the else also can be a column or the result of a function or computation. If value equals none of the ifs, then the result of the DECODE is else. If value equals if2, then the result of the DECODE is then2.This continues for virtually as many if-then pairs as you can construct. If value equals if1, then the result of the DECODE is then1 Here, value represents any column in a table (regardless of datatype) or any result of a computation, such as one date minus another, a SUBSTR of a character column, one number times another, and so on. DECODE and CASE are often used to pivot data, that is, to turn rows of data into columns of a report.ĭECODE(value, if1, then1, if2, then2, if3, then3. You can also use the CASE function and the COALESCE function to execute complex logical tests within your SQL statements. DECODE can be used for the generation of crosstab reports. It is one of several extensions Oracle added to the standard SQL language. The DECODE function is without doubt one of the most powerful in Oracle's SQL. If STATE equals 'AK,' the DECODE function returns this value.The column the DECODE function examines is named STATEĭECODE and NVL functions Example Explanation of an example of the DECODE function Here are the results when using the CUSTOMER table in the pet store schema.For your information, this name is the column alias.It applies to all rows where the value in STATE is neither 'HI' nor 'AK.' In these cases, the value shown here gets returned by the DECODE function. The second case is considered if STATE does not contain 'HI.' Here, STATE is compared to 'AK.'.If STATE is equal to 'HI', then the DECODE function returns the literal shown here.The first case looks at the value in STATE and compares it to this value (the literal 'HI').The column the DECODE function examines is named STATE.The diagram below shows an example of DECODE for the state of Hawaii HI. The final value is used when col1's value does not match any of the cases. ,else)Īdd as many case and value pairs as you need. DECODE(value, if1, then1, if2, then2, if3, then3. Traditional Programming LogicIn programming and logic, a common construction of a problem is in the pattern if (temp >32)īasic structure and usage of DECODE. If there is no match, the function returns 'Unknown'. If a match is found, the corresponding job title is returned. In this example, the DECODE function evaluates the job_code column, comparing it with the specified search values ('MGR', 'DEV', and 'HR'). SELECT first_name || ' ' || last_name AS full_name, Here is a sample query using the DECODE function: We want to retrieve the full names of all employees along with a description of their job titles. Suppose we have a table named employees with the following columns: employee_id, first_name, last_name, and job_code. If no match is found and a default value is provided, the function returns the default value otherwise, it returns NULL. If a match is found, it returns the corresponding resultN value. The DECODE function compares the expression with each searchN value sequentially. default: Optional parameter that specifies the value to be returned if no match is found.resultN: The corresponding result value to be returned if the expression matches the search value.searchN: The search value to compare with the expression.expression: The value or column to be evaluated.The syntax for the DECODE function is as follows:ĭECODE(expression, search1, result1,, ) Sql Plus Environment Lesson 8 DECODE and NVL functions Objective Modify query results using the DECODE and NVL functions DECODE Function in Oracle.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |