9/26/2023 0 Comments Decode oracle sql syntax![]() SELECT DECODE( SIGN( (list_price - average ) ), SELECT ROUND( AVG(list_price), 2) average It returns the number of products whose list prices are higher than, equal to, and less than the average list price. The following statement illustrates how to use the DECODE() function in the GROUP BY clause. In this example, we sorted the employee list by job title because we passed the character J as the first argument of the DECODE() function. ORDER BY DECODE( 'J', 'F', first_name, 'L', last_name, 'J', job_title) The following query uses the DECODE() function in the ORDER BY clause to sort the employees result set based on an input argument: SELECT , 'CA', 'Canada', 'CH', 'Switzerland', 'IT', 'Italy', country_id) country ,Ĭountry_id Code language: SQL (Structured Query Language) ( sql ) B) Oracle DECODE with ORDER BY example You can use the DECODE() function to make the country data more meaningful as follows: SELECT DECODE(country_id, 'US', 'United States', 'UK', 'United Kingdom', 'JP', 'Japan' The following statements return the country that has at least two locations stored in the database: SELECT See the following locations table in the sample database: A) Use DECODE() function to make data more meaningful Let’s take some examples of using the DECODE() function to see how it works. In other words, the DECODE() function never evaluates a search (si+1) when a previous search (si) equals e. , or sn) only before comparing it to the first argument (e), rather than evaluating all search values. The DECODE() function evaluates each search value (s1, s2. You can use expressions for the search (s), the result (r), and default value (d) in the DECODE() function. The DECODE() function returns a value with the data type of the first result (r1, r2. dĭ is an expression to return when e does not equal to any searched value s1, s2. The r1, r2, …, or rn is the expression to return when e is equal to s. Note that s2, s3, … sn are automatically converted to the data type of s1 before comparing. The s1, s2, … or sn is an expression to search for. The function automatically converts e to the data type of s1 before comparing. The first argument e is the value to be searched. , ) Ĭode language: SQL (Structured Query Language) ( sql ) Arguments e The following illustrates the syntax of the Oracle DECODE() function: DECODE (e, s1, r1. The query works like the following if-then-elsif-else statement: IF 3 = 1 THENĬode language: SQL (Structured Query Language) ( sql ) Oracle DECODE() function syntax The query returned: Not one or two Code language: SQL (Structured Query Language) ( sql ) If you want to specify a default value when the function does not find any match, you do it as follows: SELECT DECODE( 3, 1, 'One', 2, 'Two', 'Not one or two') It works like the following if-then-elsif statement: IF 2 = 1 THEN If they are equal, the function returns the fifth argument (Two). ![]() Otherwise, it compares the first argument with the fourth argument (2). If the first argument equals the second one, the function returns the third argument (One). In this example, the function compares the first argument (2) with the second one. ![]() The result is: Two Code language: SQL (Structured Query Language) ( sql ) What if you want to compare the first argument with a list of arguments? See the following example: SELECT DECODE( 2, 1, 'One', 2, 'Two') It works like the following if-then-else statement: IF 1 = 2 THENĮND IF Code language: SQL (Structured Query Language) ( sql ) If you want to specify a default value when the first argument is not equal to the second one, you append the default value to the argument list as shown below: SELECT DECODE( 1, 2, 'One', 'Not one') SELECT DECODE( 1, 2, 'One')Ĭode language: SQL (Structured Query Language) ( sql ) The query returns a null value because one does not equal two. The following example is slightly different from the one above. It works like the following if statement IF 1 = 1 THENĮND IF Code language: SQL (Structured Query Language) ( sql ) Because they are equal, the function returns the third argument which is the string 'One': SELECT DECODE( 1, 1, 'One')ĭual Code language: SQL (Structured Query Language) ( sql ) In the following example, the Oracle DECODE() function compares the first argument (1) with the second argument (1). The Oracle DECODE() function allows you to add the procedural if-then-else logic to the query. Summary: in this tutorial, you will learn how to use the Oracle DECODE() function to embed if-then-else logic in SQL queries.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |