Use of the decode function and casewhen in Oracle

DECODE () function, which compares the input number with the parameter list in the function and returns a corresponding value based on the input. The parameter list of a function is a sequence of several numbers and their corresponding results. Of course, if it fails to match any real argument sequence, the function also returns a default value. Unlike other SQL functions, the DECODE function can also identify and operate

DECODE () function, which compares the input number with the parameter list in the function and returns a corresponding value based on the input. The parameter list of a function is a sequence of several numbers and their corresponding results. Of course, if it fails to match any real argument sequence, the function also returns a default value. Unlike other SQL functions, the DECODE function can also identify and operate

DECODE () function, which compares the input value with the parameter list in the function and returns a corresponding value based on the input value. The parameter list of a function is a sequence of several numbers and their corresponding result values. Of course, if it fails to match any real argument sequence, the function also has the default return value.

Unlike other SQL functions, the DECODE function can recognize and operate null values.

Syntax: DECODE (control_value, value1, result1 [, value2, result2…] [, Default_result]);

Syntax explanation: the number that control _ value attempts to process. The DECODE function compares the value with a series of even orders to determine the return value.

Value1 is a numerical value that forms an ordinal pair. If the input value matches the value, the corresponding result value is returned. Corresponds to an empty return value. You can use the keyword NULL

Result1 is the result value of an ordinal pair.

If default_result does not match any value, the default value returned by the function.

Example 1: select decode (sign (to_number (to_char (sysdate, 'mm')-6), 1, 'second half ',-1, 'First half', 0, 'july 11') as MM from dual;

The sign () function returns 0, 1, and-1 based on the value of the expression 0, positive, or negative;

Dual is a virtual table of oracle, for example, select sysdate from dual to query the current time;

This SQL statement queries the current system time based on the database. The month of the time is converted to char type, and then to number type,

When the monthly value is 6 or greater than 0, the second half of the year is displayed. If the value is less than 0, the last year is displayed. If the value is 0, the last year is displayed.

Example 2: An employee table, employees table, and department table, administrative departments;

The employee table includes employee ID employee_id, employee name employee_name, employee salary, and department code department_id;

The Department table has the Department ID department_id and department name department_name;

Department question Description: find out the number of employees in each department with a salary greater than 3000 and a small value equal to 3000.

SQL statement:

select d.department_id,        sum(decode(sign(e.salary - 3000),1,1,-1,0)) maxSal,        sum(decode(sign(3000 - e.salary),1,1,-1,0)) minSal        from employees e, departments d       where e.department_id = d.department_id       group by d.department_id;

SQL parsing: When the salary is less than or equal to 3000, 0 is returned, so use-1 instead of less than or equal to (0 and-1.

Another solution (use case when... then... end ):

select d.department_id,        sum(case when e.salary - 3000 > 0 then 1 else 0 end ) maxSal ,        sum(case when 3000 - e.salary > 0 then 1 else 0 end ) minSal        from employees e, departments d        where e.department_id = d.department_id        group by d.department_id; 

When case when has multiple conditions, use case when... then... end

select m.guid as ID,        case           when m.superitemid is null then           '-1'          when m.superitemid = '0' then           '-1'          else           m.superitemid        end as PID,        m.code || '-' || m.name as TEXT,        m.code,        m.name   from T_PUBMOFDEP m