![]() , ) Ĭ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. ![]() So it'll only execute one of these.Summary: in this tutorial, you will learn how to use the Oracle DECODE() function to embed if-then-else logic in SQL queries. The branches under the concatenation are mutually exclusive. Or you could ignore the above and stick with what you've got :)Īs you've noted, the optimizer is smart. If you want to know more about dynamic SQL, read: Where_clause := where_clause || ' and mf_trn_folio = :b7 ' If it's not it does the other.īuild your SQL statement based on the bind values passed. There are a couple of ways you could approach this.ġ. In fact, you could go further and remove this condition too: Provided :b5 is something other than P, the queries are equivalent. What are your thoughts on this? Have you faced such a query in past (I am sure you must had :) )? How you tackled it? I highly appreciate your inputs. I am thinking that this predicate is actually not providing any value when 'P' is passed as Bind.Īnd the performance is equivalent, whatever I pass as Bind (of course with result being similar in all tests of which I can think of.).īut I am not sure whether both the queries are semantically equivalent or not. MT",0)) AND "MF_TRN_FOLIO" IS NOT NULL AND ![]() Predicate Information (identified by operation id): |* 10 | 9 | 5 | INDEX RANGE SCAN | IDX_MF_TRANSACTIONS_NEW | 1 | |* 9 | 8 | 6 | TABLE ACCESS BY INDEX ROWID| MF_TRANSACTIONS | 1 | |* 6 | 5 | 1 | INDEX RANGE SCAN | MF_TRANSACTIONS_INDX5 | 288 | ![]() ![]() |* 5 | 4 | 2 | TABLE ACCESS BY INDEX ROWID| MF_TRANSACTIONS | 1 | | Id | Pid | Ord | Operation | Name | E-Rows | Actually oracle has generated a smart logic kind of plan with concatenation: Is the culprit, wherever we pass :b5 as 'P'. AND MF_TRN_FOLIO = Decode(:b5, 'P', mf_trn_folio, :b7)ĪND Nvl(MF_TRN_AMT, 0) = Decode(:b5, 'P', To_number(:b9), Nvl(mf_trn_amt, 0))ĪND MF_TRN_TRUNC_DT BETWEEN Trunc( SYSDATE - 20 ) AND Trunc(SYSDATE) ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |