difference between case and decode oracle

This includes the expression, search, and result arguments. Question: Is there a limit to the number of arguments that you can have in one DECODE statement? I'm getting an error, 'ORA-00939: too many arguments for function'.Īnswer: Yes, the maximum number of components that you can have in a DECODE function is 255. If yrs_of_service = 1 and 5 then return 0.06Īnswer: You will need to create a formula that will evaluate to a single number for each one of your ranges.ĭECODE(TRUNC (( yrs_of_service + 3) / 4), 0, 0.04,

Question: I need to write a DECODE statement that will return the following: DECODE can work with only scaler values but CASE can work with logical oprators, predicates and searchable subqueries.

Following is the list of differences - 1. There is a lot more that you can do with CASE, though, which DECODE cannot. The formula will evaluate to 2, if the supplier_id is between 21 and 30. Difference between DECODE and CASE: Everything DECODE can do, CASE can. The formula will evaluate to 1, if the supplier_id is between 11 and 20. The formula will evaluate to 0, if the supplier_id is between 1 and 10. In this example, based on the formula: TRUNC ((supplier_id - 1) / 10 However, you can try to create a formula that will evaluate to one number for a given range, and another number for the next range, and so on.ĭECODE(TRUNC ((supplier_id - 1) / 10), 0, 'category 1', Question: I would like to know if it's possible to use the DECODE function for ranges of numbers, ie 1-10 = 'category 1', 11-20 = 'category 2', rather than having to individually decode each number.Īnswer: Unfortunately, you can not use the DECODE function for ranges of numbers. The date example above could be modified as follows: LEAST(date1, date2) Helpful Tip #2: One of our viewers suggested using the LEAST function (instead of the DECODE function) as follows: Sales Bonuses DECODE(SIGN(actual-target), -1, 'NO Bonus for you', 0,'Just made it', 1, 'Congrats, you are a winner') The SIGN/DECODE combination is also helpful for numeric comparisons e.g. DECODE function was introduced first and CASE statement came later in Oracle Database (NetSuite backend database system). The date example above could be modified as follows: DECODE(SIGN(date1-date2), 1, date2, date1) Helpful Tip #1: One of our viewers suggested combining the SIGN function with the DECODE function as follows: What Is DECODE() DECODE ( ) vs CASE Syntax of DECODE() Query 1. The formula below would equal 0, if date1 is greater than date2: (date1 - date2) - ABS(date1 - date2) SQL Tutorial 51 Decode function in Oracle Database. Otherwise, the DECODE function should return date1.Īnswer: To accomplish this, use the DECODE function as follows: DECODE((date1 - date2) - ABS(date1 - date2), 0, date2, date1) But CASE can be used any where even as a paramtre of a function/procedureĬode: SQL> create or replace procedure pro_01(n number) is 2 begin 3 dbms_output.Question: One of our viewers wanted to know how to use the DECODE function to compare two dates (ie: date1 and date2), where if date1 > date2, the DECODE function should return date2. There is one more Important difference between CASE and DECODEĭECODE can be used Only inside SQL statement…. It is also used for multipul colms & multi conditions. Ex : select case job=’CLERK’ then ‘executive’ else ‘no’ end a from emp

it is using pl/sql equality conditionality. Ex: select decode(job,’CLERK’,’executive’,job) from emp Ĭase : implemented by if & elsif condition.

We can use any operator other than ‘=’, for e.g select deptno, case when deptno=10 and sal>1000 then ‘dept10’ else ‘dept20’ĭecode: using for reporting purpose. So if the deptno=10 then the value is replaced by dept10 and so on, Decode The default operator for comparison is ‘=’, for e.g select deptno,decode (deptno,10,’dept10′,20,dept20,default)

Difference between case and decode oracle