/
the_select_statement.html.md.erb
175 lines (133 loc) · 7.43 KB
/
the_select_statement.html.md.erb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
---
title: SELECT Statement
---
<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->
The SELECT statement allows you to filter data from the collection of object(s) returned by a WHERE search operation. The projection list is either specified as \* or as a comma delimited list of expressions.
For \*, the interim results of the WHERE clause are returned from the query.
**Examples:**
Query all objects from the region using \*. Returns the Collection of portfolios (The exampleRegion contains Portfolio as values).
``` pre
SELECT * FROM /exampleRegion
```
Query secIds from positions. Returns the Collection of secIds from the positions of active portfolios:
``` pre
SELECT secId FROM /exampleRegion, positions.values TYPE Position
WHERE status = 'active'
```
Returns a Collection of struct<type: String, positions: map> for the active portfolios. The second field of the struct is a Map ( jav.utils.Map ) object, which contains the positions map as the value:
``` pre
SELECT "type", positions FROM /exampleRegion
WHERE status = 'active'
```
Returns a Collection of struct<portfolios: Portfolio, values: Position> for the active portfolios:
``` pre
SELECT * FROM /exampleRegion, positions.values
TYPE Position WHERE status = 'active'
```
Returns a Collection of struct<pflo: Portfolio, posn: Position> for the active portfolios:
``` pre
SELECT * FROM /exampleRegion portfolio, positions positions
TYPE Position WHERE portfolio.status = 'active'
```
## <a id="concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_1B7762EC686A4808B1D12E8851954E82" class="no-quick-link"></a>SELECT Statement Results
The result of a SELECT statement is either UNDEFINED or is a Collection that implements the [SelectResults](<%=vars.javadocs%>/org/apache/geode/cache/query/SelectResults.html) interface.
The SelectResults returned from the SELECT statement is either:
1. A collection of objects, returned for these two cases:
- When only one expression is specified by the projection list and that expression is not explicitly specified using the fieldname:expression syntax
- When the SELECT list is \* and a single collection is specified in the FROM clause
2. A collection of Structs that contains the objects
When a struct is returned, the name of each field in the struct is determined following this order of preference:
1. If a field is specified explicitly using the fieldname:expression syntax, the fieldname is used.
2. If the SELECT projection list is \* and an explicit iterator expression is used in the FROM clause, the iterator variable name is used as the field name.
3. If the field is associated with a region or attribute path, the last attribute name in the path is used.
4. If names cannot be decided based on these rules, arbitrary unique names are generated by the query processor.
## <a id="concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_972EE73A6F3E4427B6A99DB4EDF5860D" class="no-quick-link"></a>DISTINCT
Use the DISTINCT keyword if you want to limit the results set to unique rows. Note that in the current version of <%=vars.product_name%> you are no longer required to use the DISTINCT keyword in your SELECT statement.
``` pre
SELECT DISTINCT * FROM /exampleRegion
```
**Note:**
If you are using DISTINCT queries, you must implement the equals and hashCode methods for the objects that you query.
## <a id="concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_25D7055B33EC47B19B1B70264B39212F" class="no-quick-link"></a>LIMIT
You can use the LIMIT keyword at the end of the query string to limit the number of values returned.
For example, this query returns at most 10 values:
``` pre
SELECT * FROM /exampleRegion LIMIT 10
```
## <a id="concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_D9DF0F785CA94EF8B367C3326CC12990" class="no-quick-link"></a>ORDER BY
You can order your query results in ascending or descending order by using the ORDER BY clause. You must use DISTINCT when you write ORDER BY queries.
``` pre
SELECT DISTINCT * FROM /exampleRegion WHERE ID < 101 ORDER BY ID
```
The following query sorts the results in ascending order:
``` pre
SELECT DISTINCT * FROM /exampleRegion WHERE ID < 101 ORDER BY ID asc
```
The following query sorts the results in descending order:
``` pre
SELECT DISTINCT * FROM /exampleRegion WHERE ID < 101 ORDER BY ID desc
```
**Note:**
If you are using ORDER BY queries, you must implement the equals and hashCode methods for the objects that you query.
## <a id="concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_69DCAD624E9640028BC86FD67649DEB2" class="no-quick-link"></a>Preset Query Functions
<%=vars.product_name%> provides several built-in functions for evaluating or filtering data returned from a query. They include the following:
<table>
<colgroup>
<col width="33%" />
<col width="33%" />
<col width="33%" />
</colgroup>
<thead>
<tr class="header">
<th>Function</th>
<th>Description</th>
<th>Example</th>
</tr>
</thead>
<tbody>
<tr>
<td>ELEMENT(expr)</td>
<td>Extracts a single element from a collection or array. This function throws a <code class="ph codeph">FunctionDomainException</code> if the argument is not a collection or array with exactly one element.</td>
<td><pre class="pre codeblock"><code>ELEMENT(SELECT DISTINCT *
FROM /exampleRegion
WHERE id = 'XYZ-1').status = 'active'</code></pre></td>
</tr>
<tr>
<td>IS_DEFINED(expr)</td>
<td>Returns TRUE if the expression does not evaluate to <a href="../query_additional/literals.html#literals__section_undefined">UNDEFINED</a>. Inequality queries include undefined values in their query results. With the IS_DEFINED function, you can limit results to only those elements with defined values.</td>
<td><pre class="pre codeblock"><code>IS_DEFINED(SELECT DISTINCT *
FROM /exampleRegion p
WHERE p.status = 'active')</code></pre></td>
</tr>
<tr>
<td>IS_UNDEFINED (expr)</td>
<td>Returns TRUE if the expression evaluates to <a href="../query_additional/literals.html#literals__section_undefined">UNDEFINED</a>. With the exception of inequality queries, most queries do not include undefined values in their query results. The IS_UNDEFINED function allows undefined values to be included, so you can identify elements with undefined values.</td>
<td><pre class="pre codeblock"><code>SELECT DISTINCT *
FROM /exampleRegion p
WHERE IS_UNDEFINED(p.status)</code></pre></td>
</tr>
<tr>
<td>NVL(expr1, expr2)</td>
<td>Returns expr2 if expr1 is null. The expressions can be query parameters (bind arguments), path expressions, or literals.</td>
<td> </td>
</tr>
<tr>
<td>TO_DATE(date_str, format_str)</td>
<td>Returns a Java Data class object. The arguments must be String S with date_str representing the date and format_str representing the format used by date_str. The format_str you provide is parsed using java.text.SimpleDateFormat.</td>
<td> </td>
</tr>
</tbody>
</table>