DECODE

[aph]

if sal = 5000 then
salary := sal*3
end if;

if sal > 5000 then
salary := sal*2
end if;

if sal < 5000 then
salary := sal*4
end if;

The above IF condition I want to convert in DECODE the reason why I need it to be in DECODE, Because I am writing SQL report so I can not use IF condition in SQL. There are 3 diferent. formulas I have to use.

------------------------------
just for your ref.

SELECT
ename,
deptno,
hiredate,
DECODE(sal,5000,sal*3) salary
FROM emp;

How to check for > and < sal in DECODE or there is another way to handle this problem???
---------------------------------------

[tamilselvan]

You can't use > or < in decode.

Use UNION operator, for example:

select emp, sal, sal*3
from emp where sal=5000
union
select emp, sal, sal*2
from emp where sal<5000
union
select emp, sal, sal*4
from emp where sal > 5000
;

[Heath]

Actually, you can play some math tricks to get your desired results using a DECODE.


    select ENAME, DEPT, HIREDATE,
           DECODE( SIGN( SAL - 5000 ), -1, SAL * 4, 0, SAL * 3, 1, SAL * 2 )
    from EMP;


HTH,

Heath

[chrisrlong]

Of course, if you are using 8i, you can simply use:

SELECT
---ENAME,
---DEPTNO,
---HIREDATE,
---CASE
------WHEN---SAL = 5000---THEN
---------SAL*3
------WHEN---SAL > 5000---THEN
---------SAL*2
------WHEN---SAL < 5000---THEN
---------SAL*4
------ELSE
---------SAL
---END
------AS---SALARY
FROM EMP;

- Chris

[rcherch]

Hey Chris,

Pretty sweet. I forgot about the CASE in Oracle. Is there any performance hit by using the CASE statement over the DECODE?

I think the CASE was added in 8i to support ansi standards.

[chrisrlong]

Performance penalty?? Not in the least. CASE is actually quite a bit faster than DECODE. So it's cleaner, more powerful and faster. The only drawback is that you can't use it in PL/SQL. This is because Oracle, in its infinite wisdom, has maintained a separate SQL engine for straight SQL versus PL/SQL and they don't always implement the latest and greatest in the PL/SQL - SQL engine. Brilliant, eh ? At least they wised up and merged the two engines in 9i. For now, however, you must use dynamic SQL to implement the CASE statement or analytical functions in PL/SQL.

- Chris

[jmodic]

Originally posted by chrisrlong
Performance penalty?? Not in the least. CASE is actually quite a bit faster than DECODE.

Chris,

What makes you think CASE is considerably faster than DECODE? Have you perform any tests? Your statement made me qurious, so I performed some tests, which showed virtualy exactly the same performance between CASE and DECODE.

Since in 8.1.* we (still) can't use CASE inside the PL/SQL I executed looping iterations using NDS. Here is the code of both tests, they both showed virtualy equal timings (around 8 secs on my computer):

declare
  y number;
begin
  for i in 1 .. 10000 loop
    y := i;
    execute immediate 
      'select CASE
               when :1 < 5000 then :1 * 4
               when :1 = 5000 THEN :1 * 3
               else :1 * 2
             end
        from dual'
    using y, y, y, y, y;
  end loop;
end;
/

declare
  y number;
begin
  for i in 1 .. 10000 loop
    y := i;
    execute immediate 
      'select DECODE( SIGN(:1  - 5000 ),
                      -1, :1 * 4,
                      0, :1 * 3,
                      1, :1 * 2 )
       from dual'
    using y, y, y, y;
  end loop;
end;
/

[marist89]

Originally posted by jmodic

Originally posted by chrisrlong
Performance penalty?? Not in the least. CASE is actually quite a bit faster than DECODE.

Chris,

What makes you think CASE is considerably faster than DECODE? Have you perform any tests?

Ouch! You should have known you can't sneak a blanket statement past jmodic...

[chrisrlong]

Never let me get away with anything, eh?

Okay, here goes...

The problem is that the vast majority of time for your test is taken up by stuff other than the DECODE. If you ran your test with neither a DECODE nor a CASE, you would likely end up with the exact same time. In other words, you need to be able to measure a delta between a baseline of just selecting a column and actually using the DECODE *before* you can possibly measure the delta between the DECODE and the CASE.

A better test might be SELECTing all the records from a large table and comparing those results. Here are 2 such statements:

BEGIN
---DBMS_OUTPUT.PUT_LINE ( 'START - ' || TO_CHAR(SYSDATE,'HH:MI:SS') );
---EXECUTE IMMEDIATE 'DROP TABLE FRED';
---EXECUTE IMMEDIATE
------'
---------CREATE TABLE
------------FRED
---------AS
------------SELECT
---------------ORG_PK,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''AK''---THEN---1---ELSE 0 END AS TEST1,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''AZ''---THEN---2---ELSE 0 END AS TEST2,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''PA''---THEN---3 ---ELSE 0 END AS TEST3,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''MD''---THEN---4 ---ELSE 0 END AS TEST4,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''CA''---THEN---5 ---ELSE 0 END AS TEST5,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''NY''---THEN---6 ---ELSE 0 END AS TEST6,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''NJ''---THEN---7 ---ELSE 0 END AS TEST7,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''FL''---THEN---8 ---ELSE 0 END AS TEST8,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''GA''---THEN---9 ---ELSE 0 END AS TEST9,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''NC''---THEN---10 ---ELSE 0 END AS TEST10
------------FROM
---------------ORG, RGLTR
------'---;
---DBMS_OUTPUT.PUT_LINE ( 'STOP - ' || TO_CHAR(SYSDATE,'HH:MI:SS') );
END;


BEGIN
---DBMS_OUTPUT.PUT_LINE ( 'START - ' || TO_CHAR(SYSDATE,'HH:MI:SS') );
---EXECUTE IMMEDIATE 'DROP TABLE FRED';
---EXECUTE IMMEDIATE
------'
---------CREATE TABLE
------------FRED
---------AS
------------SELECT
---------------ORG_PK,
---------------DECODE(---CRD_CNTCT_STATE_CD,---''AK'', 1, 0---)---AS TEST1,
---------------DECODE( ---CRD_CNTCT_STATE_CD,---''AZ'', 2, 0---)---AS TEST2,
---------------DECODE(---CRD_CNTCT_STATE_CD,---''PA'', 3, 0---)---AS TEST3,
---------------DECODE( ---CRD_CNTCT_STATE_CD,---''MD'', 4, 0---)---AS TEST4,
---------------DECODE( ---CRD_CNTCT_STATE_CD,---''CA'', 5, 0---)---AS TEST5,
---------------DECODE( ---CRD_CNTCT_STATE_CD,---''NY'', 6, 0---)---AS TEST6,
---------------DECODE( ---CRD_CNTCT_STATE_CD,---''NJ'', 7, 0---)---AS TEST7,
---------------DECODE( ---CRD_CNTCT_STATE_CD,---''FL'', 8, 0---)---AS TEST8,
---------------DECODE( ---CRD_CNTCT_STATE_CD,---''GA'', 9, 0---)---AS TEST9,
---------------DECODE( ---CRD_CNTCT_STATE_CD,---''NC'',10, 0---)---AS TEST10
------------FROM
---------------ORG, RGLTR
------'---;
---DBMS_OUTPUT.PUT_LINE ( 'STOP - ' || TO_CHAR(SYSDATE,'HH:MI:SS') );
END;

The trick is then to pick a table with enough rows that you can notice the difference yet not more rows than fit in the cache. Otherwise, you will introduce I/O issues which will likely hide the CASE benefits.

The final requirement is to perform the test on a machine where you can actually see the difference - my servers here are too powerful. I can't even distinguish between a straight column select and a decode usage. I'll try to remember to run a test tonight at home, though. I should be able to see it there.

Anyway, per the Oracle Docs:
"CASE statements are similar in purpose to the Oracle DECODE statement, but they offer more flexibility and logical power. They are also easier to read than traditional DECODE statements, and offer better performance as well. "
...
"Because this query does not require a PL/SQL function invocation, it is much faster. "

The main difference would be that a context switch is not required for each row in the statement with a CASE. Now, as I said, it is not really noticable in powerful, multi-processor servers where I/O is the only true bottleneck. But, at the microsopic level of how long a DECODE actually takes, the CASE should be faster. I have definitely noticed a speed penalty with other context switches on my machine at home, so I would assume that I could measure this there.

Make sense?

- Chris

[marist89]

Originally posted by chrisrlong
The trick is then to pick a table with enough rows that you can notice the difference yet not more rows than fit in the cache. Otherwise, you will introduce I/O issues which will likely hide the CASE benefits.

Actually, I disagree with your testing methodology. If you want to compare the raw speed of DECODE vs. CASE, it would actally be more consitent to NOT select data from the database. Introducing the database into the test brings inconsistent results because of the way the database may be tuned/sized.