Query API Overview
The query API is made up of many classes. The following are the primary parts, generally used when updating a query:
|
Field or Control |
Definition |
|---|---|
| Query |
The query definition. |
| QuerySelect |
A query SELECT statement. There can be multiple QuerySelect objects for queries that involve unions or subqueries. Each select (or union or subquery) consists of QueryRecords, QueryOutputFields, QuerySelectedFields, and QueryCriteria. |
| QueryRecords |
The records that are part of the existing QuerySelect definition. |
| QueryOutputFields |
The fields that you've selected to be displayed when the results of the query are run. |
| QuerySelectedFields |
All the fields that make up the QuerySelect Definition. These include the fields selected as output fields, and fields added as part of Query Expressions. |
| QueryCriteria |
The criteria for the query. |
| QueryExpression |
The Query expressions that can contain SQL functions or other SQL fragments. |
| QueryList |
The lists used in the in-list functionality for criteria. |
| QueryPrompt |
The prompts used in criteria. |
| QueryDBRecords and QueryDBRecordFields |
All the records available to be used as QueryRecords, and all the fields available. This list is restricted based on a user’s Query Access Security Groups. |
A database query. You must get a query before you can access any of the other query classes. You can then create a new query and save it to the database, or open an existing query and modify it.
Each query is composed of one or more select statements:
|
Field or Control |
Definition |
|---|---|
| Main Select |
The instance of the first SELECT statement of the Query is the Main Select. There can only be one Main Select statement in the Query. This instance consists of the QueryOutputFIelds, QueryCriteria, and the QueryExpressions for the Main Select statement. |
| Union |
In addition to the Main Select, a query can have one or more unions. The Unions are added by using the AddUnion method. |
| Sub-Select |
A subquery used in the criteria of the Main Select or in a Union. |
The records that are part of an existing query definition. In PeopleSoft Query, these are the records listed on the query tab. Each QuerySelect has its own set of QueryRecords.
The fields that you've selected to be part of the query definition. They're called output fields because when you run the query, these are the fields that make up the output columns.
In PeopleSoft Query, these are the fields listed on the Fields tab.
The collection of QueryOutputFields does not necessarily include all columns returned in query resultset. This collection only includes columns that have been added to query output collection using the query classes or by an end-user designing a query.
PeopleSoft Query can also add additional columns for related language processing and also for translate labels on fields, and so you cannot use the Query Output Collections as a way to discover all of the columns that are returned in the resultset or by executing the SQL generated from a query.
All the fields that make up the query definition. These include the fields selected as output fields, and fields added as QueryExpressions.
The selection criteria for the query. Each QueryCriteria object is made up of the following:
|
Field or Control |
Definition |
|---|---|
| Logical |
Any criteria objects after the first must include have a Logical value, either AND or OR. |
| Expression 1 |
A field or value that you want to base the selection criterion on, that is, Expression1 is the left-hand side of the criterion's comparison. |
| Operator |
A mathematical or other operator used to specify the relationship between Expression 1 and Expression 2. |
| Expression 2 |
A field or other value, also called a comparison value, used with Expression 1, that is, Expression2 is the right-hand side of the criterion's comparison. |
In PeopleSoft Query, a QueryCriteria is under the Criteria tab.
Expression can be made up of constant values, fields, subqueries, and so on.
A QueryDBRecord is a record in the database that can be used as a QueryRecord. The list of records is controlled by security: the only records displayed as QueryDBRecords are records accessible by the user.
The QueryDBRecordFields are the fields that make up the QueryDBRecords.
In PeopleSoft Query, the QueryDBRecords are under the Record tab. After you click on the plus sign next to a record, the QueryDBRecordFields are displayed.