Oracle decode Oracle Tips by Burleson Consulting

In older versions of Oracle where the CASE is not available use DECODE instead:

Here are some DECODE examples:

CREATE VIEW BLOCK_STATUS AS
   SELECT DECODE(state, 0, 'FREE',
          1, DECODE(lrba_seq,0, 'AVAILABLE', 'BEING USED'),
          3, 'BEING USED', state) "BLOCK STATUS",
          COUNT(*) "COUNT"

    FROM x$bh
    GROUP BY 
    decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE',    
        'BEING USED'),3,'BEING USED',state);


select DECODE(GROUPING(a.owner), 1, 'All Owners',
a.owner) AS "Owner",
count(decode( a.object_type,'TABLE',1,null)) "Tables",
count(decode( a.object_type,'INDEX' ,1,null)) "Indexes",
count(decode( a.object_type,'PACKAGE',1,null)) "Packages",
count(decode( a.object_type,'SEQUENCE',1,null)) "Sequences",
count(decode( a.object_type,'TRIGGER',1,null)) "Triggers",
count(decode( a.object_type,'PACKAGE',null,'TABLE',null,'INDEX',null,'SEQUENCE',null,'TRIGGER',null, 1)) "Other",
count(1) "Total"
from dba_objects a
group by rollup(a.owner)
/