#Decode oracle sql syntax how to
#Decode oracle sql syntax code

The first parameter is your main value or it can be your table value.Second parameter is search value which mean that second parameter value matchs with the first parameter value if it is match then third parameter return their value otherwise fourth parameter returns their value. In the DECODE() function, We pass four parameter. Let's understand the syntax so we can easily use the DECODE() function in Oracle SQL. The syntax of the DECODE function is: DECODE ( expression, search, result, search, result. Syntax of DECODE() function in OracleĭECODE(value,search_value,result,default_value) Oracle DECODE function is used in different database versions like Oracle 9i,Oracle 10g,Oracle 11g and Oracle 12c. s1, s2,, or sn is the expression to search for.

#Decode oracle sql syntax code

, ,sn,rn, d) Code language: SQL (Structured Query Language) (sql) In this syntax: e is the argument that to be searched for or compared with other argument s1, s2, sn. Oracle DECODE only use for equality check logic in Oracle SQL. The following illustrates the syntax of the SQL DECODE () function: DECODE (e, s1, r1, s2, r2.

Oracle DECODE is use for transform the data to one value to another value. Oracle DECODE is a function in Oracle which help to implement if-then-else logic in SQL query.

#Decode oracle sql syntax how to

In this tutorial, you have learned how to use the SQL DECODE() function to add the if-then-else logic to a query.In this Oracle SQL Tutorial, We will expolre the DECODE() function in Oracle. In this case, the DECODE() function returned the salary column, therefore, the result set was sorted by salary column. The DECODE() function returned the column to sort based on the input argument. In this example, we used the DECODE() function in the ORDER BY clause. We will use the employees table in the sample database for demonstration.Įmployee_id, first_name, last_name, salary d is an expression to return when e does not equal to any argument s1, s,2, … or sn.r1, r2, …, or rn is the returned result when ei = si.If any comparison e = s1, e = s2, …, e = sn return true, the DECODE() function terminates the evaluation and returns the result. The function compares the first argument with s1, s2, …, sn sequentially. s1, s2, …, or sn is the expression to search for.

The search expressions are compared to this select expression, and if there is a match then DECODE returns the result that corresponds to that search expression.

, ) Arguments expr This is the select expression. • e is the argument that to be searched for or compared with other argument s1, s2, … sn. Syntax DECODE( , , , , .

The following illustrates the syntax of the SQL DECODE() function: DECODE (e, s1, r1. This example works like the following IF-THEN-ELSEIF-ELSE statement: IF 3 = 1 THENĬode language: SQL (Structured Query Language) ( sql ) SQL DECODE() function syntax See the following example: SELECT DECODE( 3, 1, 'Equal 1,', 2, 'Equal 2', 'Not Equal 1 or 2') This example works as the following IF-THEN-ELSE IF statement: IF 2 = 1 THEN In general, you can compare the first argument of the DECODE() function with a list of arguments as shown in the following example: SELECT DECODE ( 2, 1, 'Equal 1', 2, 'Equal 2') It works like the following IF-THEN-ELSE statement: IF 1 = 2 THEN If you want to specify the value when the first argument is not equal to the second one, you use the following form of the DECODE() function: SELECT DECODE( 1, 2, 'Equal', 'Not Equal') It works like the following statement: IF 1 = 2 THEN The following example returns NULL because the first argument is not equal to the second one. Syntax and Parameters: The basic syntax for writing DECODE function in SQL is as follows: DECODE ( expression, search1, result1, search2, result2. The DECODE() function above works the same as the following IF statement: IF 1 = 1 THEN Because the first argument equals the second one, the function returns the third argument which is the string Equal. In this example, the DECODE() function compares the first argument (one) with the second argument (also one). Let’s see the following example: SELECT DECODE( 1, 1, 'Equal') Ĭode language: SQL (Structured Query Language) ( sql ) Oracle automatically converts the return value to the same data type as the first result. The SQL DECODE() function allows you to add procedure if-then-else logic to queries. Summary: in this tutorial, you will learn how to use the SQL DECODE() function to add if-then-else logic to queries.