CASE

Tested on an Oracle 11 database. The CASE expression allows you to add conditional tests to SQL statements. First create a table with a few rows of test data:

SQL> create table test_segments as
  2  select *
  3  from dba_segments where 1=2
  4  /

Table created.

SQL> insert into test_segments
  2  select * from dba_segments
  3  where bytes < 100000
  4  and owner = 'SYS'
  5  and rownum < 6
  6  /

5 rows created.

SQL> insert into test_segments
  2  select * from dba_segments
  3  where bytes >= 100000
  4  and bytes < 150000
  5  and owner = 'SYS'
  6  and rownum < 6
  7  /

5 rows created.

SQL> insert into test_segments
  2  select * from dba_segments
  3  where bytes >= 150000
  4  and owner = 'SYS'
  5  and rownum < 6
  6  /

5 rows created.

SQL>

A very simple CASE expression might have a couple of WHEN conditions. If none of these are satisfied, the output will be blank:

SQL> select segment_name,
  2  case
  3   when bytes < 100000 then 'SMALL'
  4   when bytes < 150000 then 'MEDIUM'
  5  end as object_size
  6  from test_segments
  7  order by 1
  8  /

SEGMENT_NAME                   OBJECT_SIZE
------------------------------ -----------
C_USER#
I_CCOL1
I_CDEF3                        SMALL
I_COBJ#                        MEDIUM
I_COL3
I_FILE1                        SMALL
I_IND1                         MEDIUM
I_OBJ1
I_OBJ2
I_TAB1                         SMALL
I_TS#                          SMALL
I_USER#                        MEDIUM
I_USER2                        MEDIUM
I_VIEW1                        MEDIUM
UNDO$                          SMALL

15 rows selected.

SQL> 

To get round this, you can add an ELSE at the end, if you wish:

SQL> select segment_name,
  2  case
  3   when bytes < 100000 then 'SMALL'
  4   when bytes < 150000 then 'MEDIUM'
  5   else 'LARGE'
  6  end as object_size
  7  from test_segments
  8  order by 1
  9  /

SEGMENT_NAME                   OBJECT_SIZE
------------------------------ -----------
C_USER#                        LARGE
I_CCOL1                        LARGE
I_CDEF3                        SMALL
I_COBJ#                        MEDIUM
I_COL3                         LARGE
I_FILE1                        SMALL
I_IND1                         MEDIUM
I_OBJ1                         LARGE
I_OBJ2                         LARGE
I_TAB1                         SMALL
I_TS#                          SMALL
I_USER#                        MEDIUM
I_USER2                        MEDIUM
I_VIEW1                        MEDIUM
UNDO$                          SMALL

15 rows selected.

SQL>

And you can even have nested CASE expressions. Also note how the CASE may, or may not, be followed immediately by a column name:

SQL> select segment_name,
  2  case segment_type
  3   when 'TABLE' then
  4   case
  5    when bytes < 100000 then 'SMALL TABLE'
  6    when bytes < 150000 then 'MEDIUM TABLE'
  7    else 'LARGE TABLE'
  8   end
  9   when 'INDEX' then
 10   case
 11    when bytes < 100000 then 'SMALL INDEX'
 12    when bytes < 150000 then 'MEDIUM INDEX'
 13    else 'LARGE INDEX'
 14   end
 15  end as size_and_type
 16  from
 17  test_segments
 18  order by 1
 19  /

SEGMENT_NAME                   SIZE_AND_TYPE
------------------------------ -------------
C_USER#
I_CCOL1                        LARGE INDEX
I_CDEF3                        SMALL INDEX
I_COBJ#                        MEDIUM INDEX
I_COL3                         LARGE INDEX
I_FILE1                        SMALL INDEX
I_IND1                         MEDIUM INDEX
I_OBJ1                         LARGE INDEX
I_OBJ2                         LARGE INDEX
I_TAB1                         SMALL INDEX
I_TS#                          SMALL INDEX
I_USER#                        MEDIUM INDEX
I_USER2                        MEDIUM INDEX
I_VIEW1                        MEDIUM INDEX
UNDO$                          SMALL TABLE

15 rows selected.

SQL>