![]() If we try to execute the following query statement with the DECODE function shown below, then it returns the NULL value – SELECT DECODE (500,600," Five Hundred") FROM dual The above query statement works similar to the logic of following the if-else logic program shown below – Internally the PL/ SQL compares the two parameters 500 and 500, which are equal, and hence as the condition evaluates to true, it returns the string Five Hundred mentioned in the third parameter. The output of the above query statement after execution is as shown below – Example #1Ĭonsider the following query statement in PL/ SQL: SELECT DECODE (500, 500, "Five Hundred") FROM dual Let us try to understand the working of the DECODE function with the help of simple examples. ![]() When they are specified as expressions, their value is evaluated only when we have to do a comparison which involves those expression participants, or else, they are not evaluated when specified. Note: All these parameters search expressions, return values, and the default value can be expressions. IF comparison evaluates to false by all the search expression comparisons, then the decode function returns the default value as the output of the function. Return value n – If the comparison of expression and any of the search expression revaluates to true, then its corresponding return valuer is sent as an output.ĭefault value – If for all the mentioned search expressions the comparison evaluates to false, then the NULL value is returned from the decode function if this default value parameter is not specified. Return value 1, Return value 2, Return value 3, …. Which means that if a comparison of expression and search expression r-1 evaluates to true, then all the expressions starting from search expression r until last are not even converted to compatible datatypes for conversion. Note: If for any of the search expressions the comparison evaluates to true, then there is no datatype conversion taking place for the further search expressions. Each of the search expressions is firstly converted to the appropriate datatype and then compared with the expression. Search expression n – This is the expressions with which the expression will be compared one by one. Search expression 1, Search expression 2, Search expression 3, …. If they evaluate to true, the return value is returned or else if default value is specified then it is returned else NULL is return if a comparison of the expression and the search expression evaluates false. Before comparing this value, it is converted into the datatype of the search expression 1. The terminologies used in the above syntax are as described here –Įxpression or value – This is the literal value of an expression or a column name of the table, which we have to compare with the search expressions. The syntax of the DECODE function in the PL/ SQL Oracle database is as shown in the below description – DECODE (expression/value, search expression 1, return value 1, ….
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |