![]() ![]() Consider the following query statement – SELECT DECODE (300, 600, "Six Hundred", 500, "Five Hundred"," Not even Five or six hundred") FROM dual We can even try providing the default value when neither of the search expression matches with the specified expression. The above query statement behaves in the same manner as shown in the below program of PL/ SQL of If else if ladder. Let us try executing the following query statement of decode function – SELECT DECODE (500, 600, "Six Hundred", 500, "Five Hundred") FROM dual Let us consider an example for that scenario as well. We can also provide more than one search expression for comparison in that case, our decode function will be behaving the same as that of the if-else if ladder. The working of the above decode function is internally similar to following if-else condition – IF 500=600 THEN This is because when the comparison of the first two arguments evaluates to false, then as no default value is mentioned, the last parameter, the return value by default for false evaluation, is NULL which is given as output. The output of the execution of the above query statement is as shown below – 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. DECODE (expression/value, search expression 1, return value 1, …. ![]()
0 Comments
Leave a Reply. |