On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

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