Case Examples

With this data, these are fairly nonsensical examples, but the basic logic is some from real code.

---

Example 1:

Column NewJob Format A15 -
            Heading 'New Job Desc'

Select EmpNo,
      EName,
      -------------------------------
           Case When Job = 'CLERK'
                Then 'Admin Assistant'
                Else InitCap(Job)
            End
            As
      NewJob
      -------------------------------
 From Emp
Where DeptNo = 30;

---

Example 2:

Column NewGrade   Format A5  Heading 'New|Grade'

Select EmpNo,
      EName,
      Sal,
      ----------------------------------------
           Case When (Sal >= 1000)
                 And (Sal &lt= 1500)
                Then '0'
                --------------------
                When (Sal >  1500)
                Then (
                      Select To_Char(Grade)
                        From SalGrade
                       Where Sal Between LoSal
                                     and HiSal
                      )
                --------------------
                Else '?'
             End
             As
      NewGrade
      --------------------------------------
 From Emp
Where DeptNo = 30;

---

Example 3:

Column DataLen Format A6 Heading 'Data|Length'

Select Column_Name,
      Data_Type,
      ------------------------------------------------------
               Case When (Data_Precision is Null)
                    Then To_Char(Data_Length,'999')
                    Else ' '||Data_Precision||'.'||Data_Scale
                End
               As
      DataLen
      ------------------------------------------------------
 From User_Tab_Columns
Where Table_Name = 'EMP';

---

Example 4:

Column CommStatus Format A16 Heading 'Commissioned|Status'


Select EmpNo, EName, Sal, Comm,
      -------------------------------
               Case When Comm is Null
                      Or Comm = 0
                    Then 'Not Commissioned'
                    Else 'Commissioned'
                End
                As
      CommStatus
      -------------------------------
 From Emp
Where DeptNo = 30;

Example 5:

Select Ename, Job, Deptno, Mgr,
       --------------------------------------
             Case When Job In ('MANAGER' ,  'SALESMAN' )
                  Then 
                       Case When DeptNo = 10
                            Then 'Type A'
                            Else 'Type B'
                        End
                  Else 
                       Case When Mgr = 7698
                            Then 'Type C'
                            Else 'Type D'
                        End
              End
              As
       Type
       --------------------------------------
  From Emp;

See Oracle documentation for further detail:
Case expressions