Decode function usage in Oracle

There are two main types:

Meaning explanation:
Decode (condition, value 1, return value 1, value 2, return value 2,... Value N, return value N, default value)

The function has the following meanings:
IF condition = value 1 Then
RETURN (translation value 1)
elsif condition = value 2 Then
RETURN (translation value 2)
......
elsif condition = value n Then
RETURN (translated value N)
ELSE
RETURN (default value)
END IF

-————————————————

Select Decode (x, 1, ' x is 1 ', 2, ' X was 2 ', ' others ') from dual

When x equals 1 o'clock, return ' x is 1 '.

When x equals 2 o'clock, return ' x is 2 '.

Otherwise, return ' others '.

—————————————————

Decode (field or Field operation, value 1, value 2, value 3)

The result of this function is that when the value of the field or Field operation is equal to the value 1 o'clock, the function returns the value 2, otherwise the value 3
Of course, a value of 1, a value of 2, a value of 3 can also be an expression, this function makes some SQL statements a lot simpler

Common uses of the Decode function: grouping by field contents, sorting by field contents, fixing row to column

1. Using Decode for grouping by field content

Sometimes grouping can be more complex, such as the need to merge some field content grouping, so that simple group by is not,

But use case or decode to get the results you expect.

Example:

-Create a simple list of employees and corresponding jobs

DROP TABLE T1;

CREATE TABLE T1

(

ID Number (10),

NAME VARCHAR2 (10),

JOB VARCHAR2 (20)

);

INSERT into T1 VALUES (1, ' Jack ', ' VP ');

INSERT into T1 VALUES (2, ' Tony ', ' CEO ');

INSERT into T1 VALUES (3, ' Merry ', ' VP ');

INSERT into T1 VALUES (4, ' James ', ' Operation ');

INSERT into T1 VALUES (5, ' Linda ', ' Operation ');

COMMIT;

It is now necessary to count the number of VP and above, the number of ordinary employees, and this is the use of a simple group by JOB

No, it's easy to use decode to achieve it.

SELECT DECODE (Job, ' VP ', ' Vp_ceo ', ' CEO ', ' vp_ceo ', ' operation ') JOB,

COUNT (*) job_cnt

from T1

GROUP by DECODE (JOB, ' VP ', ' Vp_ceo ', ' CEO ', ' vp_ceo ', ' operation ');

The results are as follows:

JOB job_cnt
----------- --------------
Vp_ceo 3
Operation 2

2. Using Decode to sort by field content

This can happen in daily development, such as a table with a id,name field (ID is the proxy primary key),

You need to specify the collation by name value (for example, name is a character ' part '), so how do you sort it?

Sort by field contents and specify dynamic columns by field content

-Build a test table

DROP TABLE T2;

CREATE TABLE T2

(

ID number,

Dept_name VARCHAR2 (10),

region_id Number (10)

);

INSERT into T2 VALUES (1, ' Depta ', 12);

INSERT into T2 VALUES (2, ' Depta ', 10);

INSERT into T2 VALUES (3, ' Depta ', 9);

INSERT into T2 VALUES (4, ' Depta ', 7);

INSERT into T2 VALUES (5, ' DEPTB ', 12);

INSERT into T2 VALUES (6, ' DEPTB ', 13);

INSERT into T2 VALUES (7, ' DEPTB ', 22);

INSERT into T2 VALUES (8, ' DEPTB ', 9);

INSERT into T2 VALUES (9, ' DEPTC ', 8);

INSERT into T2 VALUES (, ' DEPTC ', 10);

INSERT into T2 VALUES (one, ' DEPTC ', 11);

COMMIT;

-A. Sort by field contents

Requirements: Sorted by department Dept_name (A->B->C), region_id Ascending by region within each department

Analysis: Here the department dept_name is not a number (VARCHAR2), direct sorting when not,

If you can convert each value of the dept_name to a corresponding number, then sort it out.

SELECT id,dept_name,region_id

From T2

ORDER by DECODE (Dept_name,

' Depta ', 1,

' DEPTB ', 2,

3),

region_id;

-B. Specifying dynamic column sorting by field content

Requirements: If Dept_name is Depta, sort by ID in ascending order, otherwise by region_id ascending order

SELECT id,dept_name,region_id

From T2

ORDER by DECODE (Dept_name,

' Depta ', ID,

REGION_ID);

3. Using decode to implement fixed row to column

-Simple Employee work statistics

DROP TABLE T3;

CREATE TABLE T3

(

Student_no Number (10),

Student_name VARCHAR2 (10),

Course_type VARCHAR2 (10),

Course_score Number (10)

);

INSERT into T3 VALUES (1, ' Jack ', ' 中文版 ', 80);

INSERT into T3 VALUES (1, ' Jack ', ' Chinese ', 90);

INSERT into T3 VALUES (1, ' Jack ', ' math ', 85);

INSERT into T3 VALUES (2, ' Tony ', ' 中文版 ', 70);

INSERT into T3 VALUES (2, ' Tony ', ' Chinese ', 95);

INSERT into T3 VALUES (2, ' Tony ', ' math ', 80);

COMMIT;

To implement row-to-column:

SELECT Student_name,decode (course_type, ' 中文版 ', course_score) from T3;

What is the difference between grouping and non-grouping in analyzing this statement?

SELECT Student_name,

MAX (DECODE (Course_type, ' 中文版 ', Course_score)) 中文版,

MAX (DECODE (course_type, ' Chinese ', Course_score)) Chinese,

MAX (DECODE (course_type, ' math ', course_score)) Math

from T3

GROUP by Student_name;

Why do I need Max?

Because to implement row-to-column, grouped by field, there must be a grouping function for non-grouped columns in decode, of course min, SUM

The AVG and other group functions can also be implemented, MAX, min for any type, SUM, AVG can only be numeric type

Decode function usage in Oracle