| --- |
| 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](/releases/latest/javadoc/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> |