Building SQL Queries with Query Builder
To build SQL queries with Query Builder, use the Query Builder component (EQRY_SAMP_HTML_CMP) and the Pre-defined Queries component (EQRY_VIEWER_CMP).
These topics provide an overview of Query Builder and discuss how to build SQL queries with Query Builder.
|
Page Name |
Definition Name |
Usage |
|---|---|---|
|
EQRY_OUTPUT_PG |
Select the object attributes that you want to use as result attributes in the query results. Select as many attributes as needed. |
|
|
Save Attributes Page |
EQRY_OUT_S_PG |
Save the result attributes of the query. Enter a name and description for the query. The query is available to all users who have access to Query Builder. If you don't want the query generally available, select Private Query. |
|
EQRY_OUT_L_PG |
Load or append predefined result attributes selection for use in the current query. The attributes that are available depend on the target object that is selected when Query Builder is invoked. |
|
|
EQRY_ISCRIPT |
Specify the attributes for which you want to query, and define expressions by using operators and values. |
|
|
Criteria Attribute Tree Page |
EQRY_ATT_TREE_PG |
Use the object selection tree, navigate to the attribute that you want to use as query criteria, and select it. |
|
Select Value Page |
EQRY_VBUILD_PG |
Select the attribute value to use as query criteria. |
|
Save Criteria Page |
EQRY_PROP_PG |
Save the current criteria selection by name for use in later queries. The system saves the query criteria selection for the target object that is selected when Query Builder is invoked. |
|
Load Criteria Page |
EQRY_LOAD_PG |
Load an existing criteria selection. |
|
EQRY_RESULT_PG |
Review the results of your query. |
|
|
EQRY_COMBO_SAVE_PG |
Save the current query or load a predefined query. |
|
|
EQRY_VIEWER_PG |
Run a query that you've predefined on the Query Builder component. The page looks like the Query Builder - Results page. |
Query Builder is a flexible tool that is used to query class objects that are defined in the PeopleSoft HCM metadata repository.
Query Builder lets you:
Create queries of unlimited length and complexity without the need to know the underlying database model.
Query builder automatically generates the most efficient SQL based on the metadata class definitions, generates all SQL joins automatically based on the relationships defined in metadata, and includes SetID and effective-dated logic.
Select any number of available output fields to display results from both the underlying class and the class relationships.
Modify current queries to include different output and criteria.
Run previously defined queries.
Navigate to objects that are returned as query results.
Group the output with counts, sums, and averages and include minimums and maximums for the grouped output.
Query Builder automatically generates the appropriate SQL for aggregations.
Preview the Structured Query Language (SQL) query generated by Query Builder.
Whereas PeopleSoft Query uses records and fields in queries, Query Builder queries objects and attributes. This approach has two benefits:
You are more likely to recognize an object name than record and field names, and hence find it easier to query objects that are in the system.
Underlying record and field definitions of system objects can change without affecting existing query definitions, resulting in more robust queries.
Use the Result Attributes page (EQRY_OUTPUT_PG) to select the object attributes that you want to use as result attributes in the query results.
Select as many attributes as needed.
Navigation:
This example illustrates the fields and controls on the Result Attributes page. You can find definitions for the fields and controls later on this page.
Use the Result Attributes page to select the object attributes that you want to search against. You can select any combination of the defined attributes for the object.
At any point while defining result attributes, you can run the query and view the results. You can then return to the Result Attributes page to modify the query result attributes, go to the Criteria page to refine the query selection, or save the definitions.
Field or Control |
Description |
|---|---|
Class ID to Query |
Displays the class ID you selected for querying on the search page. |
Select Attributes
The system displays folders for the target class ID object and any other objects that have a relationship (as entered on the Object Class Definition page) with the selected class ID. In this example, you can see that both the Person Core Objects and Person Data Model objects have a relationship with the Email Addresses object.
Click the folder to view the attributes of each object. Click an attribute to add it to the attribute grid.
Field or Control |
Description |
|---|---|
Select Attributes |
You can rearrange the order of the selected attributes to determine the order in which they appear on the Results page. You can also determine the sort order of the data that is in each results column. |
Hide |
Hides the attribute tree. |
More... |
When Query Builder first opens, the Select Attributes tree displays the system objects that are closely related to the target object through direct association. These relationships are defined in HCM metadata and represent the objects that you most often use in queries. If you can't find the objects that you want, click the More link to access other objects . |
Expand All |
Click to reveal the contents of all attribute tree folders. |
Collapse All |
Click to collapse all attribute tree folders. |
Attribute Grid |
Select this check box to display the results columns as defined in the Attribute Grid. |
Aggregations |
Select this check box to display the results based on the groupings that are selected in the Aggregations grid. |
Enable Outer Joins |
Select this check box to enable the query to use outer joins to gather additional data in the query. |
View Results |
Click to run the query. The system opens the Results page, showing the results of the query. |
Clear Attributes |
(Optional) Click to remove the attributes that appear in the Selected Attributes list. |
Set As Default |
(Optional) Click to set the attributes that you select as the default attributes that appear each time that you use the target object. |
Save Attributes |
(Optional) Click to save current attribute selections as a query. |
Load Attributes |
(Optional) Click to load predefined attribute selections. |
Attribute Grid
Field or Control |
Description |
|---|---|
Selected Attributes |
The system populates the Selected Attributes list with the target object's list box items as they're defined in the Tools Record definition. You can add or delete attributes as necessary for your query. |
|
Use the arrows to rearrange the attributes to determine the column display order in query results. For example, the attribute at the top of the Selected Attributes list appears as the left-most column in the query results. |
Sort |
(Optional) Indicate a sort order for the attribute in the column results: Blank, Asc (ascending), and Desc (descending). You can sort one or multiple columns. Specifying multiple sorts causes column results to sort from left to right. Column results that are sorted have a link that appears in the column header in the query results. |
and 
Aggregations
Use the check boxes in this grid to indicate how you want the results to appear on the Results page. For example, you can tell the system to group the results by order number, and within each group, sum the order amounts as well as show the minimum and maximum amounts.
Field or Control |
Description |
|---|---|
Group |
Select this check box to group the results by the attribute. |
Count, Sum, Avg (average), Min (minimum), and Max (maximum) |
Select these options to have the system count the different groups. For attributes that are related to transaction amounts, the system can also sum, average, and indicate the minimum or maximum amounts. |
Use the Load Attributes page (EQRY_OUT_L_PG) to load or append predefined result attributes selection for use in the current query.
The attributes that are available depend on the target object that is selected when Query Builder is invoked.
Navigation:
Click the Load Attributes button on the Result Attributes page.
This example illustrates the fields and controls on the Load Attributes page. You can find definitions for the fields and controls later on this page.
Use the Load Attributes page to add saved results columns to the query. The page displays all of the attribute sets that are saved by all users as well as the attribute sets that you save and mark as private.
Field or Control |
Description |
|---|---|
Load |
Replaces the result attributes in the query. |
Append |
Adds the result attributes onto the query. |
User ID |
The user who creates and saves the result attributes. |
Private Query |
Indicates the private queries. |
Use the Query Builder - Criteria page (EQRY_ISCRIPT) to specify the attributes for which you want to query, and define expressions by using operators and values.
Navigation:
This example illustrates the fields and controls on the Query Builder - Criteria page. You can find definitions for the fields and controls later on this page.
After you load or define the query result attributes, you can optionally add criteria to filter the query results on the Query Builder - Criteria page. You can only select attributes from the objects that are selected on the Results Attribute page. Specify as many criteria rows as needed, or save a criteria selection for later use to use as a predefined query selection in the current query.
By using indentation in expressions, you can create queries that are as complex as you need. Query Builder intelligently parenthesizes the expressions to ensure that the query is evaluated correctly.
Field or Control |
Description |
|---|---|
Attribute Name |
Select the attributes from the objects connected to the class ID you're querying. Click the Select an Attribute button to view a list of all defined attributes for the object on the Select Criteria page. Select an attribute to return to the Criteria page. |
Operator |
Select an operator to define the condition for the criteria. |
Value |
Enter a value for the selected attribute to use as query criteria. |
Selecting a Value
Access the Select Value page to select the values that you want to use to filter the query. The fields on the page differ depending on the operator that you select on the Criteria page.
If you use the between or not between operators, you select a From and To value.
If you used the like, not like, equal to, not equal, greater than, less than or equal to, less than, or greater than or equal to, you select a value for the system to compare the result to.
If you use the in or not in operator, you can select one or more check boxes for the values within which you want the system to return the results.
At any point while defining the criteria, you can click the View Results button to run the query, and then return to the Criteria or Results Columns page to modify or (optionally) save the definitions.
Working with Criteria
Field or Control |
Description |
|---|---|
View Results |
Click to view the results of the query on the Results page. |
Clear Criteria |
Click to remove all current attribute names and value selections and start over. |
Save Criteria |
(Optional) Click to save current attribute name and value selections. After selecting the criteria, you can save the query definition for future use. On the Save Criteria page, enter a name and description for the query and indicate whether or not you want it to be a private query. |
Load Criteria |
(Optional) Click to load predefined attribute name and value selection. This page works just like the Load Columns page, and displays all of the query criteria sets that are saved by all users as well as the sets that you save and mark as private. |
Use the Results page (EQRY_RESULT_PG) to review the results of your query.
Navigation:
This example illustrates the fields and controls on the Query Builder - Results page. You can find definitions for the fields and controls later on this page.
Use the Query Builder - Results page to view the results of the query. You can click any of the links that are in the results columns to go to the application page where the attribute is defined.
Select to view the query SQL, download the query results into Excel, add new criteria, or rerun the query.
Note: When you select to download the current query results to a Microsoft Excel spreadsheet, only the results currently displayed on the page are downloaded to the spreadsheet. To download all query results, click the View All link, and then click the Download to Excel link.
Aggregations Grid
Shows the results sorted and totaled based on the check boxes that you select on the Results Columns page. This grid is only visible if you select the Aggregations check box on the Results Columns page.
Use the Query Builder - Save/Load Query page (EQRY_COMBO_SAVE_PG) to save the current query or load a predefined query.
Navigation:
This example illustrates the fields and controls on the Query Builder - Save/Load Query page. You can find definitions for the fields and controls later on this page.
While defining a query, you can save the result attributes and criteria as separate definitions. This allows you the flexibility to mix and match various result attributes and criteria. You can also save a query definition, which is a combination of an output (result attributes) selection and a criteria selection.
Field or Control |
Description |
|---|---|
Query Name |
Enter a name under which to save the query. When you save a Query Builder query, both the result attributes and the criteria selection are saved. |
Private Query |
Select to make the query private and available only to the user who created it. |
Load |
Click load to load a predefined query. |
Use the Pre-defined Queries page (EQRY_VIEWER_PG) to run a query that you've predefined on the Query Builder component.
The page looks like the Query Builder - Results Page.
Navigation:
Select a query by searching on output and criteria combination, description, original class ID, or a combination of these. Then click the query result that you want to run. Click the View Definition link to access the Query Builder component, where you can change the results columns, attributes, or criteria of the query and rerun the query.
Note: Predefined queries are also available by loading existing query definitions from within Query Builder.