Simple CASE WHEN functions can only deal with some simple business scenarios, while CASE WHEN conditional expressions are written more flexibly.

CASE WHEN conditional expression function: Similar to IF ELSE statement in JAVA.

Simple CASE WHEN function:

CASE SCORE WHEN 'A' THEN 'excellent' ELSE 'Fail,' END
CASE SCORE WHEN 'B' THEN 'good' ELSE 'Fail,' END
CASE SCORE WHEN 'C' THEN 'in' ELSE 'Fail,' END

Equally, CASE WHEN conditional expression function is used to implement:

CASE WHEN SCORE = 'A' THEN 'excellent'
     WHEN SCORE = 'B' THEN 'good'
     WHEN SCORE = 'C' THEN 'in' ELSE 'Fail,' END

The value behind THEN should be the same as that behind ELSE, otherwise an error will be reported. As follows:

CASE SCORE WHEN 'A' THEN 'excellent' ELSE 0 END

If the data types of'You'and'0' are inconsistent, an error will be reported:

[Err] ORA-00932: Data type inconsistency: should be CHAR, but obtained NUMBER

Simple CASE WHEN functions can only deal with some simple business scenarios, while CASE WHEN conditional expressions are written more flexibly.

CASE WHEN conditional expression function: Similar to IF ELSE statement in JAVA.

Format:

CASE WHEN condition THEN result
 
[WHEN...THEN...]
 
ELSE result
 
END

Conditionis an expression that returns a Boolean type. If the expression returns true, the entire function returns the corresponding result value. If the expression is false, the result value after ElSE is returned. If the ELSE clause is omitted, NULL is returned.

Here are some common scenarios.

SELECT
    STUDENT_NAME,
    (CASE WHEN score < 60 THEN 'Fail,'
        WHEN score >= 60 AND score < 80 THEN 'pass'
        WHEN score >= 80 THEN 'excellent'
        ELSE 'abnormal' END) AS REMARK
FROM
    TABLE

Note: If you want to judge whether score is null, WHEN score = null THEN'absence test', which is a wrong way of writing, the correct way of writing should be:

CASE WHEN score IS NULL THEN 'Absent examination' ELSE 'normal' END

SELECT 
	SUM (CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT,
	SUM (CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,
	SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,
	SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS
FROM 
	THTF_STUDENTS

SELECT 
	E_CODE,
	SUM(CASE WHEN E_TYPE = 0 THEN E_VALUE ELSE 0 END) AS WATER_ENERGY,--Water consumption
	SUM(CASE WHEN E_TYPE = 1 THEN E_VALUE ELSE 0 END) AS ELE_ENERGY,--power consumption
	SUM(CASE WHEN E_TYPE = 2 THEN E_VALUE ELSE 0 END) AS HEAT_ENERGY--Heat consumption
FROM 
	THTF_ENERGY_TEST
GROUP BY

When the energy consumption value is less than 10, the value of P_PRICE when P_LEVEL=0 is greater than 10, and the value of P_PRICE when P_LEVEL=1 is less than 30.

CASE WHEN energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 0)
    WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 1)
    WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 2) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 2)