Case usage in Oracle

1. Method 1: simple variables;

Select ename,
(Case deptno
When 10 then 'accounting'
When 20 then 'Research'
When 30 then 'sales'
When 40 then 'operation'
Else 'unassigned'
End) as department
From EMP;
Ename Department
--------------------
Smith Research
Allen unassigned
Ward sales
Jones Research
Martin sales
Blake sales
Clark Accounting
Scott Research
King Accounting
Turner sales
Adams Research
James sales

2. Second usage: it can be called a conditional expression;

Select ename,Sal , Deptno,
Case
When Sal <= 500 then 0
When SAL> 500 and Sal <1500 then 100
When SAL> = 1500 and Sal <2500 and deptno = 10 then 200
When SAL> 1500 and Sal <2500 and deptno = 20 then 500
When Sal >=2500 then 300
Else 0
End "bonus"
From EMP;
Ename Sal deptno bonus
----------------------------------------
Smith 800 20 100
Allen 1600 90 0
Ward 1250 30 100
Jones 2975 20 300
Martin 1250 30 100
Blake 2850 30 300
Clark 2450 10 200

3. In some cases, you can also use the decode () function, which is interchangeable with the case usage.

For example: Select decode (x, 1, 'x is 1', 2, 'x is 2', 'others ') from dual

If X is equal to 1, 'x is 1' is returned '.

If X is equal to 2, 'x is 2' is returned '.

Otherwise, 'others 'is returned '.

For example, if you want to judge whether a is 0, false is displayed; otherwise, true is displayed.

This can be written as follows: Decode (A, 0, false, true)