forked from docs/doc-exports
Su, Xiaomeng
76a5b1ee83
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>
94 lines
9.9 KiB
HTML
94 lines
9.9 KiB
HTML
<a name="dli_spark_decode1"></a><a name="dli_spark_decode1"></a>
|
|
|
|
<h1 class="topictitle1">decode1</h1>
|
|
<div id="body8662426"><p id="dli_spark_decode1__en-us_topic_0000001703779953_p18471163815215">This function is used to implement if-then-else branch selection.</p>
|
|
<div class="section" id="dli_spark_decode1__en-us_topic_0000001703779953_section45291954203217"><h4 class="sectiontitle">Syntax</h4><pre class="screen" id="dli_spark_decode1__en-us_topic_0000001703779953_screen19928459112517">decode1(<expression>, <search>, <result>[, <search>, <result>]...[, <default>])</pre>
|
|
</div>
|
|
<div class="section" id="dli_spark_decode1__en-us_topic_0000001703779953_section992014913317"><h4 class="sectiontitle">Parameters</h4>
|
|
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" id="dli_spark_decode1__en-us_topic_0000001703779953_table1829154762513" frame="border" border="1" rules="all"><caption><b>Table 1 </b>Parameters</caption><thead align="left"><tr id="dli_spark_decode1__en-us_topic_0000001703779953_row8830104792517"><th align="left" class="cellrowborder" valign="top" width="21.05%" id="mcps1.3.3.2.2.5.1.1"><p id="dli_spark_decode1__en-us_topic_0000001703779953_p983074711252">Parameter</p>
|
|
</th>
|
|
<th align="left" class="cellrowborder" valign="top" width="9.58%" id="mcps1.3.3.2.2.5.1.2"><p id="dli_spark_decode1__en-us_topic_0000001703779953_p6830124732517">Mandatory</p>
|
|
</th>
|
|
<th align="left" class="cellrowborder" valign="top" width="12.659999999999998%" id="mcps1.3.3.2.2.5.1.3"><p id="dli_spark_decode1__en-us_topic_0000001703779953_p19322199181910">Type</p>
|
|
</th>
|
|
<th align="left" class="cellrowborder" valign="top" width="56.71000000000001%" id="mcps1.3.3.2.2.5.1.4"><p id="dli_spark_decode1__en-us_topic_0000001703779953_p08301547132513">Description</p>
|
|
</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody><tr id="dli_spark_decode1__en-us_topic_0000001703779953_row15830184792511"><td class="cellrowborder" valign="top" width="21.05%" headers="mcps1.3.3.2.2.5.1.1 "><p id="dli_spark_decode1__en-us_topic_0000001703779953_p683034714250">expression</p>
|
|
</td>
|
|
<td class="cellrowborder" valign="top" width="9.58%" headers="mcps1.3.3.2.2.5.1.2 "><p id="dli_spark_decode1__en-us_topic_0000001703779953_p12830184752518">Yes</p>
|
|
</td>
|
|
<td class="cellrowborder" valign="top" width="12.659999999999998%" headers="mcps1.3.3.2.2.5.1.3 "><p id="dli_spark_decode1__en-us_topic_0000001703779953_p83221595199">All data types</p>
|
|
</td>
|
|
<td class="cellrowborder" valign="top" width="56.71000000000001%" headers="mcps1.3.3.2.2.5.1.4 "><p id="dli_spark_decode1__en-us_topic_0000001703779953_p18653154615263">Expression to be compared</p>
|
|
</td>
|
|
</tr>
|
|
<tr id="dli_spark_decode1__en-us_topic_0000001703779953_row38408436572"><td class="cellrowborder" valign="top" width="21.05%" headers="mcps1.3.3.2.2.5.1.1 "><p id="dli_spark_decode1__en-us_topic_0000001703779953_p14840104385718">search</p>
|
|
</td>
|
|
<td class="cellrowborder" valign="top" width="9.58%" headers="mcps1.3.3.2.2.5.1.2 "><p id="dli_spark_decode1__en-us_topic_0000001703779953_p208406435579">Yes</p>
|
|
</td>
|
|
<td class="cellrowborder" valign="top" width="12.659999999999998%" headers="mcps1.3.3.2.2.5.1.3 "><p id="dli_spark_decode1__en-us_topic_0000001703779953_p15477819494">Same as that of <strong id="dli_spark_decode1__en-us_topic_0000001703779953_b58979498573">expression</strong></p>
|
|
</td>
|
|
<td class="cellrowborder" valign="top" width="56.71000000000001%" headers="mcps1.3.3.2.2.5.1.4 "><p id="dli_spark_decode1__en-us_topic_0000001703779953_p14840154355711">Search item to be compared with <strong id="dli_spark_decode1__en-us_topic_0000001703779953_b15792131195810">expression</strong></p>
|
|
</td>
|
|
</tr>
|
|
<tr id="dli_spark_decode1__en-us_topic_0000001703779953_row1780125617571"><td class="cellrowborder" valign="top" width="21.05%" headers="mcps1.3.3.2.2.5.1.1 "><p id="dli_spark_decode1__en-us_topic_0000001703779953_p881156115716">result</p>
|
|
</td>
|
|
<td class="cellrowborder" valign="top" width="9.58%" headers="mcps1.3.3.2.2.5.1.2 "><p id="dli_spark_decode1__en-us_topic_0000001703779953_p2815565572">Yes</p>
|
|
</td>
|
|
<td class="cellrowborder" valign="top" width="12.659999999999998%" headers="mcps1.3.3.2.2.5.1.3 "><p id="dli_spark_decode1__en-us_topic_0000001703779953_p19322299192">All data types</p>
|
|
</td>
|
|
<td class="cellrowborder" valign="top" width="56.71000000000001%" headers="mcps1.3.3.2.2.5.1.4 "><p id="dli_spark_decode1__en-us_topic_0000001703779953_p19811656105716">Return value when the values of <strong id="dli_spark_decode1__en-us_topic_0000001703779953_b69508235161">search</strong> and <strong id="dli_spark_decode1__en-us_topic_0000001703779953_b1848615283165">expression</strong> match</p>
|
|
</td>
|
|
</tr>
|
|
<tr id="dli_spark_decode1__en-us_topic_0000001703779953_row1680891015815"><td class="cellrowborder" valign="top" width="21.05%" headers="mcps1.3.3.2.2.5.1.1 "><p id="dli_spark_decode1__en-us_topic_0000001703779953_p12808101012580">default</p>
|
|
</td>
|
|
<td class="cellrowborder" valign="top" width="9.58%" headers="mcps1.3.3.2.2.5.1.2 "><p id="dli_spark_decode1__en-us_topic_0000001703779953_p3808710105817">No</p>
|
|
</td>
|
|
<td class="cellrowborder" valign="top" width="12.659999999999998%" headers="mcps1.3.3.2.2.5.1.3 "><p id="dli_spark_decode1__en-us_topic_0000001703779953_p17562054918">Same as that of <strong id="dli_spark_decode1__en-us_topic_0000001703779953_b654985613570">result</strong></p>
|
|
</td>
|
|
<td class="cellrowborder" valign="top" width="56.71000000000001%" headers="mcps1.3.3.2.2.5.1.4 "><p id="dli_spark_decode1__en-us_topic_0000001703779953_p3808191019581">If all search items do not match, the value of this parameter is returned. If no search item is specified, <strong id="dli_spark_decode1__en-us_topic_0000001703779953_b1867117451583">NULL</strong> is returned.</p>
|
|
</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</div>
|
|
<div class="section" id="dli_spark_decode1__en-us_topic_0000001703779953_section210162513312"><h4 class="sectiontitle">Return Values</h4><p id="dli_spark_decode1__en-us_topic_0000001703779953_p184124317231"><strong id="dli_spark_decode1__en-us_topic_0000001703779953_b151810587588">result</strong> and <strong id="dli_spark_decode1__en-us_topic_0000001703779953_b166324235912">default</strong> are return values. These values can be of any data type.</p>
|
|
<div class="note" id="dli_spark_decode1__en-us_topic_0000001703779953_note229110261456"><img src="public_sys-resources/note_3.0-en-us.png"><span class="notetitle"> </span><div class="notebody"><ul id="dli_spark_decode1__en-us_topic_0000001703779953_ul18360434753"><li id="dli_spark_decode1__en-us_topic_0000001703779953_li1636010340519">If they match, the value of <strong id="dli_spark_decode1__en-us_topic_0000001703779953_b108115318591">result</strong> is returned.</li><li id="dli_spark_decode1__en-us_topic_0000001703779953_li1236019346511">If no match is found, the value of <strong id="dli_spark_decode1__en-us_topic_0000001703779953_b17902658175912">default</strong> is returned.</li><li id="dli_spark_decode1__en-us_topic_0000001703779953_li1360203418514">If <strong id="dli_spark_decode1__en-us_topic_0000001703779953_b1385511331407">default</strong> is not specified, <strong id="dli_spark_decode1__en-us_topic_0000001703779953_b385614331702">NULL</strong> is returned.</li><li id="dli_spark_decode1__en-us_topic_0000001703779953_li1736015349511">If the search options are duplicate and matched, the first value is returned.</li></ul>
|
|
</div></div>
|
|
</div>
|
|
<div class="section" id="dli_spark_decode1__en-us_topic_0000001703779953_section13277192233920"><h4 class="sectiontitle">Example Code</h4><div class="p" id="dli_spark_decode1__en-us_topic_0000001703779953_p192561436181512">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:<pre class="screen" id="dli_spark_decode1__en-us_topic_0000001703779953_screen14256736141516">CREATE EXTERNAL TABLE salary (
|
|
dept_id STRING, -- Department
|
|
userid string, -- <em id="dli_spark_decode1__en-us_topic_0000001703779953_i5873814143521">Employee ID</em>
|
|
sal INT
|
|
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
|
|
stored as textfile;</pre>
|
|
</div>
|
|
<p id="dli_spark_decode1__en-us_topic_0000001703779953_p72563368150">Adds the following data:</p>
|
|
<pre class="screen" id="dli_spark_decode1__en-us_topic_0000001703779953_screen4256836111510">d1,user1,1000
|
|
d1,user2,2000
|
|
d1,user3,3000
|
|
d2,user4,4000
|
|
d2,user5,5000</pre>
|
|
<p id="dli_spark_decode1__en-us_topic_0000001703779953_p78421456161511"><strong id="dli_spark_decode1__en-us_topic_0000001703779953_b7852204817016">Example</strong></p>
|
|
<p id="dli_spark_decode1__en-us_topic_0000001703779953_p118421456161513">Returns the name of each department.</p>
|
|
<p id="dli_spark_decode1__en-us_topic_0000001703779953_p106151520325">If <strong id="dli_spark_decode1__en-us_topic_0000001703779953_b094916118110">dept_id</strong> is set to <strong id="dli_spark_decode1__en-us_topic_0000001703779953_b139201991419">d1</strong>, <strong id="dli_spark_decode1__en-us_topic_0000001703779953_b206836121010">DLI</strong> is returned. If it is set to <strong id="dli_spark_decode1__en-us_topic_0000001703779953_b8385429811">d2</strong>, <strong id="dli_spark_decode1__en-us_topic_0000001703779953_b8891173213117">MRS</strong> is returned. In other scenarios, <strong id="dli_spark_decode1__en-us_topic_0000001703779953_b13116438912">Others</strong> is returned.</p>
|
|
<pre class="screen" id="dli_spark_decode1__en-us_topic_0000001703779953_screen13747426123220">select dept, decode1(dept, 'd1', 'DLI', 'd2', 'MRS', 'Others') as result from sale_detail;</pre>
|
|
<p id="dli_spark_decode1__en-us_topic_0000001703779953_p660141515324">Returned result:</p>
|
|
<pre class="screen" id="dli_spark_decode1__en-us_topic_0000001703779953_screen1661616405326">d1 DLI
|
|
d2 MRS
|
|
d3 Others
|
|
d4 Others
|
|
d5 Others</pre>
|
|
<p id="dli_spark_decode1__en-us_topic_0000001703779953_p1060115123215"></p>
|
|
</div>
|
|
</div>
|
|
<div>
|
|
<div class="familylinks">
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="dli_08_0476.html">Other Functions</a></div>
|
|
</div>
|
|
</div>
|
|
|