A conditional expression, mainly appearing in clauses such as WHERE, HAVING and CASE, is used to judge whether a value satisfies a certain condition. Here are its most commonly seen types:
(1) Comparison expression
|
Syntax |
Description |
|
= |
Equality |
|
<> |
Inequality |
|
>、<、>=、<= |
Greater than, less than, greater than or equal to, less than or equal to |
|
BETWEEN ... AND ... |
Judge whether a value is within a range or not (boundary values are included) |
|
IN (Value1, Value2, ...) |
Set member matching |
|
LIKE |
Fuzzy matching, where % matches any characters and _ matches a single character |
|
IS NULL , IS NOT NULL=NULL, <>NULL |
Judge whether a specified expression is null or not. |
(2) Logical expression
|
Syntax |
Description |
|
AND |
Logical AND operator returns true if and only if both of its operands are true |
|
OR |
Logical OR operator returns true if at least one of the operands is true |
|
NOT |
Logical NOT operator inverts the truth value of a logical expression |
1） CASE conditional expression
Syntax:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END
Example:
Ø Query the record where ContactTitle is Owner and Contact is headed by Mr.
SELECT
CustID,CustName,Contact,ContactTitle,CityCode
FROM
Customer
WHERE
ContactTitle = 'Owner'
AND
Contact like 'Mr%'
Ø Query information of the employee where CityCode is 30101,30201,50101.
SELECT
CustID,CustName,Contact,ContactTitle,CityCode
FROM
Customer
WHERE
([30101,30201,50101]).contain(CityCode)
Ø According to the Bools field value type, convert values to the corresponding numbers to enter Gender and Marital Status fields.
SELECT
EID, Dept,
CASE Bools
WHEN 0 THEN 'unmarried man'
WHEN 1 THEN 'married man'
WHEN 2 THEN 'unmarried woman'
ELSE 'married woman'
END
AS "Gender and Marital Status"
FROM
EMPS
Learning points:
1) contain() Besides the regular greater than, less than and equal used for single value judgment, you can also use esProc functions in conditional expressions, such as contain used in the above example.
2) A string must be enclosed by single quotation marks.