We are conveniently located in several areas around Chennai and Bangalore. You can use this Decode function in various Oracle versions or PLSQL versions such as Oracle 9i, Oracle 10g, Oracle 11g and Oracle 12c. If in case the expression is returned null, oracle also returns null, i.e the first result search.Ī Decode function in SQL will be 255, which is the maximum permissible number of components including the expression argument, search argument and the result argument. Oracle considers two nulls as one and the same. Suppose, your first result is returned is a null or it contains the data type CHAR, oracle converts that returned value to the VARCHAR2 data type. Let’s get a clear idea of this concept with an example. Then, it converts the returned value to that same data type as the first result. Before comparing, Oracle converts the expression and search values of that data type of the first searched value. The evaluation ends in case if a previous search us equal to the expression. Unlike evaluating all search values directly this method evaluates all the search values before comparing it to expression. Generally, Oracle DB follows a method called the Short-circuit evaluation method.

This data type will be present in the same character set as in the very first result parameter. The string returned will be a data type, VARCHAR2. The Expression, Search and the obtained results could be some of the following data types namely CHAR, NCHAR, VARCHAR2, and NVARCHAR2. Suppose if expression and the search were the character data, in that case, Oracle compares both through a technique called non-padded comparison semantics. It lets the first expression with the highest number precedence in order to identify the argument and then converts the other remaining arguments indirectly to that particular data type and returns that data type. If the first search result pair is a number, Oracle starts comparing the entire search result expressions. Numeric Types:īINARY_FLOAT or BINARY_DOUBLE and a NUMBER will fall under Numeric types. If you omit the default returned value, then, Oracle returns null. If there is no match found in the search, the default value will be returned. Suppose, if an expression is found equal, the same is returned by the Oracle DB. It compares an expression with each search value one after another. What is the use of the Decode Function in SQL?Īs we discussed earlier, a Decode function in Oracle helps us to add if-then-else logic to a query procedurally. We shall try to understand the Decode function, its syntax through examples. This tutorial will help you to learn the use of SQL Decode function so as to add the if-then-else logic to your queries.

Decode oracle plsql