Monday, June 23, 2008

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

No comments:

Post a Comment