Other functions - IoT Platform - Alibaba Cloud Documentation Center

The SQL analysis feature provides other functions that are commonly used in the development process. You can call the functions based on your business requirements. This topic describes the syntax and parameters of the functions, such as CAST, DECODE, and LEAST. This topic also provides examples on how to call the functions.

Function	Description
BETWEEN AND expression	Returns the values that fall in or fall out of a specified range.
CASE WHEN expression	Returns values based on the computing result of an expression.
CAST	Converts the result of an expression into a specified data type.
COALESCE	Returns the first non-null value in the parameter list.
DECODE	Implements the `IF-THEN-ELSE` logic.
GREATEST	Returns the maximum value of the input parameters.
IF	Checks whether a specified condition is true.
LEAST	Returns the minimum value of the input parameters.
NULLIF	Checks whether the values of two input parameters are the same.
NVL	Specifies the return values of the parameters whose values are null.

BETWEEN AND expression

Syntax
```
<a> [NOT] between <b> and <c>
```
Description
Returns the values of field a. The values must fall in or fall out of the range between b and c.
Description
- a: required. This parameter specifies the field whose values you want to obtain.
- b and c: required. The two parameters specify a value range. The data types of the two parameters must be the same as the data type of the a parameter.
Return value
The values that fall in or fall out of the specified range are returned.
If the a, b, or c parameter is empty, null is returned.
Examples
Query the data whose sal is from 1000 to 1500. Sample statement:
```
select * from emp where sal between 1000 and 1500;
```

CASE WHEN expression

Syntax

The SQL analysis feature supports case when expressions in the following formats:

case <value>
when <value1> then <result1>
when <value2> then <result2>
...
else <resultn>
end

case
when (<_condition1>) then <result1>
when (<_condition2>) then <result2>
when (<_condition3>) then <result3>
...
else <resultn>
end

Description
Returns the value of result based on the calculation result of value or _condition.
Parameters
- value: required. The value that is used for comparison.
- _condition: required. The condition that is used for comparison.
- result: required. The return value.
Return value
- If the data types of all result values are only BIGINT and DOUBLE, the values are returned after their data types are converted into the DOUBLE type.
- If result values of the STRING type exist, the values are converted into the STRING type before they are returned. If a data type conversion is not supported, an error is returned. For example, data of the BOOLEAN type cannot be converted into the STRING type.
- Conversions between other data types are not allowed.

Examples

Return the value of result based on the data in the sale_detail table that contains the shop_name (STRING), customer_id (STRING), and total_price (DOUBLE) columns. Data in the table:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

Sample statement:

select 
case  
when region='china' then 'default_region'
when region like 'shang%' then 'sh_region'
end as region 
from sale_detail;

Sample result:

+------------+
| region     |
+------------+
| default_region |
| default_region |
| default_region |
| sh_region  |
| sh_region  |
| sh_region  |
+------------+

CAST

Syntax
```
cast(<expr> as <type>)
```
Description
Converts the type of a data source that is specified by the expr parameter into the type that is specified by the type parameter.
Parameters
- expr: required. The data source whose data type you want to convert.
- type: required. The data type into which you want to convert the data. Usage:
  - cast(double as bigint): converts a value of the DOUBLE type into the BIGINT type.
  - cast(string as bigint): converts a value of the STRING type into the BIGINT type. If the string consists of numerals that are expressed in the INTEGER form, the string is converted into the BIGINT type. If the string consists of numerals that are expressed in the FLOAT or EXPONENTIAL form, the string is converted into the DOUBLE type and then into the BIGINT type.
  - The default date format, yyyy-mm-dd hh:mi:ss, is used for cast(string as datetime) and cast(datetime as string).
Return value
- A value of the specified data type is returned.
Examples
- Example 1: common usage. Sample statement:
```
-- The return value is 1. 
select cast('1' as bigint);
```
- Example 2: Convert a value of the STRING type into the BOOLEAN type. If the value of the STRING type is an empty string, false is returned. Otherwise, true is returned. Sample statement:
  - The value of the STRING type is an empty string.
```
select cast("" as boolean);
-- The return value is false.
+------+
| _c0  |
+------+
| false |
+------+
```
  - The value of the STRING type is a non-empty string.
```
select cast("false" as boolean);
-- The return value is true.
+------+
| _c0  |
+------+
| true |
+------+
```

COALESCE

Syntax
```
coalesce(<expr1>, <expr2>, ...)
```
Description
Returns the first non-null value in <expr1>, <expr2>, ....
Description
expr: required. The values that you want to check.
Return value
The data type of the return value is the same as the data type of the input parameter.

Examples

Common usage. Sample statement:

-- The return value is 1. 
select coalesce(null,null,1,null,3,5,7);

DECODE

Syntax

decode(<expression>, <search>, <result>[, <search>, <result>]...[, <default>])

Description
Implements the IF-THEN-ELSE logic.
Description
- expression: required. The expression that you want to compare.
- search: required. The search item that is used to compare with expression.
- result: required. The value that is returned when the value of search matches the value of expression.
- default: optional. If no search item matches the expression, the value of default is returned. If no value is specified for this parameter, null is returned.
Note
- Except for the null values, all other values of the result parameter must be of the same data type. If the values are of different data types, an error is returned.
- The values of search and expression must be of the same data type. Otherwise, an error is returned.
Return value
- If a search item matches the expression, the value of result is returned.
- If no search item matches the expression, the value of default is returned.
- If no value is specified for the default parameter, null is returned.
- If duplicate search items match the expression, the value of the first search item is returned.
- In most cases, the SQL analysis feature returns null as the calculation result of the NULL=NULL equation. However, this function determines that the NULLs in the equation have the same value.

Examples

Return the value of result based on the data in the sale_detail table that contains the shop_name (STRING), customer_id (STRING), and total_price (DOUBLE) columns. Data in the table:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

Sample statement:

-- If the value of customer_id is c1, Taobao is returned. If the value is c2, Alipay is returned. If the value is c3, Aliyun is returned. If the value is null, N/A is returned. In other cases, Others is returned. 
select
decode(customer_id,
'c1', 'Taobao',
'c2', 'Alipay',
'c3', 'Aliyun',
Null, 'N/A',
'Others') as result
from sale_detail;
-- The preceding statement is equivalent to the following statement: 
if customer_id = c1 then
result := 'Taobao';
elsif customer_id = c2 then
result := 'Alipay';
elsif customer_id = c3 then
result := 'Aliyun';
...
else
result := 'Others';
end if;

Sample result:

+------------+
| result     |
+------------+
| Others     |
| Others     |
| Others     |
| Taobao     |
| Alipay     |
| Aliyun     |
+------------+

GREATEST

Syntax
```
greatest(<var1>, <var2>[,...])
```
Description
Returns the maximum value of the input parameters.
Description
var1 and var2: required. Values of the BIGINT, DOUBLE, DECIMAL, DATETIME, or STRING type.
Return value
- The maximum value of the input parameters is returned. If implicit conversions are not performed, the return value is of the same data type as the input parameters.
- The value null is interpreted as the minimum value.
- If the input parameters are of different data types, the input parameters of the DOUBLE, BIGINT, DECIMAL, and STRING types are converted into the DOUBLE type for comparison, and the input parameters of the STRING and DATETIME types are converted into the DATETIME type for comparison. Implicit conversions of other data types are not allowed.

IF

Syntax

if(<testCondition>, <valueTrue>, <valueFalseOrNull>)

Description
Checks whether testCondition is true. If testCondition is true, the value of valueTrue is returned. Otherwise, the value of valueFalseOrNull is returned.
Description
- testCondition: required. The expression that you want to evaluate. The value is of the BOOLEAN type.
- valueTrue: required. The value that is returned when testCondition is true.
- valueFalseOrNull: the value that is returned when testCondition is false. You can set this parameter to null.
Return value
The data type of the return value is the same as the data type of valueTrue or valueFalseOrNull.

Examples

-- The return value is 200. 
select if(1=2, 100, 200);

LEAST

Syntax
```
least(<var1>, <var2>[,...])
```
Description
Returns the minimum value of the input parameters.
Description
var: required. The values of the input parameters. Values of the BIGINT, DOUBLE, DECIMAL, DATETIME, or STRING type.
Return value
- The minimum value of the input parameters is returned. If implicit conversions are not performed, the return value is of the same data type as the input parameters.
- If a data type conversion is performed between the DOUBLE, BIGINT, and STRING types, a value of the DOUBLE type is returned. If a data type conversion is performed between the STRING and DATETIME types, a value of the DATETIME type is returned. If a data type conversion is performed between the DECIMAL, DOUBLE, BIGINT, and STRING types, a value of the DECIMAL type is returned. Implicit conversions of other data types are not allowed.
- The value null is interpreted as the minimum value.
- If the values of all input parameters are null, null is returned.

Examples

-- The return value is 2. 
select least(5, 2, 7);

NULLIF

Syntax
```
T nullif(T <expr1>, T <expr2>)
```
Description
Compares the values of expr1 and expr2. If the values are the same, null is returned. If the values are different, the value of expr1 is returned.
Description
expr1 and expr2: required. Expressions of any data type. T specifies the type of input data. The type can be a data type supported by the SQL analysis feature.
Return value
The value of expr1 or null is returned.

Examples

-- The return value is 2. 
select nullif(2, 3);
-- The return value is null. 
select nullif(2, 2);
-- The return value is 3. 
select nullif(3, null);

NVL

Syntax
```
nvl(T <value>, T <default_value>)
```
Description
Returns default_value if value is null. Otherwise, value is returned. The value and default_value parameters must be of the same data type.
Description
- value: required. The input parameter. T specifies the type of input data. The type can be a data type supported by the SQL analysis feature.
- default_value: required. The value that is used to replace null. The data type of default_value must be the same as the data type of value.

Examples

A table named t_data contains three columns: c1 string, c2 bigint, and c3 datetime. The c1 column is of the STRING type. The c2 column is of the BIGINT type. The c3 column is of the DATETIME type. Data in the table:

+----+------------+------------+
| c1 | c2 | c3 |
+----+------------+------------+
| NULL | 20 | 2017-11-13 05:00:00 |
| ddd | 25 | NULL |
| bbb | NULL | 2017-11-12 08:00:00 |
| aaa | 23 | 2017-11-11 00:00:00 |
+----+------------+------------+

After the NVL function is called, the null value in c1 is returned as 00000, the null value in c2 is returned as 0, and the null value in c3 is returned as a hyphen (-). Sample statement:

select nvl(c1,'00000'),nvl(c2,0),nvl(c3,'-') from nvl_test;
-- Sample result: 
+-----+------------+-----+
| _c0 | _c1 | _c2 |
+-----+------------+-----+
| 00000 | 20 | 2017-11-13 05:00:00 |
| ddd | 25 | - |
| bbb | 0 | 2017-11-12 08:00:00 |
| aaa | 23 | 2017-11-11 00:00:00 |
+-----+------------+-----+