Su, Xiaomeng 76a5b1ee83 dli_sqlreference_20240227

Reviewed-by: Pruthi, Vineet <vineet.pruthi@t-systems.com>
Co-authored-by: Su, Xiaomeng <suxiaomeng1@huawei.com>
Co-committed-by: Su, Xiaomeng <suxiaomeng1@huawei.com>

2024-03-27 22:02:33 +00:00

9.9 KiB

Raw Permalink Blame History

decode1

This function is used to implement if-then-else branch selection.

Syntax

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

Parameters

**Table 1** Parameters
Parameter	Mandatory	Type	Description
expression	Yes	All data types	Expression to be compared
search	Yes	Same as that of expression	Search item to be compared with expression
result	Yes	All data types	Return value when the values of search and expression match
default	No	Same as that of result	If all search items do not match, the value of this parameter is returned. If no search item is specified, NULL is returned.

Return Values

result and default are return values. These values can be of any data type.

If they match, the value of result is returned.
If no match is found, the value of default is returned.
If default is not specified, NULL is returned.
If the search options are duplicate and matched, the first value is returned.

Example Code

To help you understand how to use functions, this example provides source data and function examples based on the source data. Run the following command to create the salary table and add data:

CREATE EXTERNAL TABLE salary (
dept_id STRING, -- Department
userid string, -- Employee ID
sal INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
stored as textfile;

Adds the following data:

d1,user1,1000
d1,user2,2000
d1,user3,3000
d2,user4,4000
d2,user5,5000

Example

Returns the name of each department.

If dept_id is set to d1, DLI is returned. If it is set to d2, MRS is returned. In other scenarios, Others is returned.

select dept, decode1(dept, 'd1', 'DLI', 'd2', 'MRS', 'Others') as result from sale_detail;

Returned result:

d1 DLI
d2 MRS
d3 Others
d4 Others
d5 Others

Parent topic: Other Functions

9.9 KiB Raw Permalink Blame History