PeopleSoft Query – Tip Sheet

Here are some tips, suggestions, and principles for working with PS Query.  

Also take a look at the following pages:

Encyclopedia/ Glossary page for query terminology

Query  FAQs and Troubleshooting page

Query External References – Hints, Tricks and Tips

1. Queries may contain sensitive information and output should be treated accordingly.
2. User access: 
   • Each user’s access to data is based on their overall security in PeopleSoft.
   • Generally, you can only access data in Query that you can see on a PeopleSoft page/panel.
3. Public Queries:  A public query is accessible when:
   • You have been granted access to the public query; and
   • You have access to all records used by the public query.
4. Data that is extracted from the databases should never be publicly shared without full review by the department that owns the data.
5. Never download confidential information to the hard drive of a laptop, your desktop, or to any other device.  Always save to a network drive.
6. Do not delete a query that you have not created.
7. Do not modify a query that you have not created.  Do a SAVE AS with a new name.
8. SAVE your query frequently as you are working on it.  If something occurs that kicks you out or if your computer freezes, you will be very glad that you did!
9. Wesleyan Query names must begin with a W.
   • For more information, see Intro to Query, Lesson 10.  
   • There can be no spaces in the name but you can use underscores.
10. Wes Functions – Using a Wes Function (in an Expression) is faster than adding a table.
11. When doing a search or setting criteria, you can use wildcards:  A percent sign (%) in the place of a one or more characters or an underscore (_) for just one character.
12.  Joins  –
    • If at all possible, join on related records. Related Records are records/tables that have their relationships/joins established on the database side, rather than defined in the query itself.  While query does a good job of detecting the appropriate join conditions, it’s not perfect, so be sure the joins make sense.  
    • The fewer the tables that need to be joined, the better:  Reducing the number of tables will reduce the time it takes for the query to run
    • Adding a description: 
      • If you need to add a description for a field in your query, you may want to add another table that contains the Description field.
      • However, note that asking for description fields from other tables (or Translate Table values) by adding more joins adds more overhead. 
      • Where possible, use a Wes Function to supply a description.
    • Not all tables can be successfully joined. 
      • If there are no fields that match up, you will return either no data or a very large amount of meaningless data (Cartesian join).
      • You can join fields in two tables by using an intervening table that has fields from each.
      • There are rare times when identical fields have different names in different tables.  In an instance such as that, you can perform a Manual Join.  Look at this page to see an example of How to Create a Manual Join.
    • The procedure for joining tables differs depending on how the tables that are being joined are related to each other. Query Manager recognizes three types of joins: record hierarchy, related record, and any record.
      • A Record Hierarchy join joins a parent table to a child table; the records have a one-to-many relationship.  (A child table is a table that uses all the same key fields as its parent, plus one or more additional keys.)
      • In a Related Record join, you can automatically join two records based on a relationship that has been predefined in the record designer.  The records have a one-to-one relationship.
      • Using Query Manager, you can create a join between two records (Any Record join) by selecting your initial base record, defining its output fields and associated criteria, and then returning to the Records page to select the second record. When you return to the Records page, you will see the link named Join Record rather than Add Record to the right of all listed record names. With the Enable Auto Join preference selected in the Query Preferences page, PeopleSoft Query automatically attempts to join the new record to the existing record by looking for matching columns on the two records.
13. Table Ordering –
    • It is important to realize that the order in which tables are added to the query affects the joins that are produced.   It is possible to create a Cartesian product situation, and that will definitely lead to slow execution and affect other users.
    • Put the smallest file last (i.e., the smallest recordset being returned).   There are built-in optimizers that make the code more efficient, but in most cases it helps the query run better if tables that are expected to return the fewest rows are added last.  The SQL statements that PS Query builds are evaluated from the bottom up. (However, it will not correct a bad (or invalid) join.)
14.  Criteria (for more details, read Grouping Criteria and Boolean Expressions and Criteria Properties) –
    • Put constant value criteria in last.  If you haven’t done so, you can reorder criteria (with the Reorder Criteria button) so that constant values come after join criteria
    •  Note that the Reorder Criteria button does not appear on the Criteria page if there is only one row of criteria
    • Order of Processing Criteria – Understanding how Query Manager processes criteria – in a certain order based on the way in which the system applies Boolean expressions – enables you to retrieve the results that you need.  Query Manager uses the following rules when processing criteria:
      • Criteria with enclosed parenthesis are evaluated
      • Followed by NOT criteria
      • Followed by AND criteria
      • Followed by OR criteria
    •  The more you put in for Criteria, the quicker the query will run
    • Query Manager enables you to add criteria to a query in multiple ways (from the Query, Fields or Expression pages).  Note that you can also add criteria directly on the Criteria page, but the user must then take the extra step of selecting the field for expression 1, which might be a considerable task on larger records.
    • Condition Type of “is null” –
      • Null is the absence of any data. 
      • Null is not the same as zeroes or blank spaces. 
      • Never select is null with character or number fields.  For example, if you are working with a number field, you could use the Condition Type  of “less than” and Expression 2 of “0” (zero).
15. Columns (Fields) – Only pull the columns into the query that you need. Not all tables are populated in PeopleSoft, and not all fields in all records/tables are populated.  Also, note that you do not have to show a Field  to use it in the Criteria.
    
16.  Preventing a runaway query – Be sure to join all appropriate key fields to ensure that the query does not time out.
17.  You can use the same record more than once in the same query.  See the next item.
18. Summarizing data – if you need a summary of the information in multiple rows, for example, you might want to know how many students you have in each Program and Plan. You can query for this kind of summary information using the Count aggregate function.  In addition, through one of the following methods, you can display the names of the identifying fields along with the count.
    • You can join another instance of the record/table containing the field being counted and select that field for display.
    • You do not need to add in the second instance, however.  Instead, you can create an expression that contains the field being counted, being sure to click on Use as Field when doing so. 
    • Click here to read about Connected Query which is a useful option when creating reports summarizing data.
19.  Records and views that begin with SCC have information related to Campus Community, e.g., SCC_PRI_NAME_VW – Current Primary Name View and SCC_PERS_SA_VW – PERSON record
20. After initially building a query, the first time it is run will be slower than on subsequent attempts.
21. Query Properties page
    • The first time that you save a new query, the Query Properties page appears to enable you to document the query. 
    • Be aware, however, that you cannot save queries using the Query Properties page.
22. Fields with Translate Values – 
    • When you edit a field that has translate values, the Edit Field Properties page displays translate value options.  The default value is “N.”  You can select whether the output displays the  short (S) or long (L) translate value.
    • Translate tables are effective-dated, so you must select which effective date to use for them.  For most tables, PeopleSoft Query defaults to the current date, meaning that it uses the currently active list of Translate table values.  However, if the table you’re querying is also effective-dated, PS Query uses the value in the EFFDT field for a row.  That is, for each row that the query returns, PS Query uses the Translate table values that were active as of the row’s effective date.
    • If neither of these effective date options are what you want, you have two more options:
      • If the table you query includes another date field, use the value in that field as the effective date for Translate table values.  Click the Edit button, select the field option, and then select the field name from the drop-down list box.
      • Use an expression to set the effective date for the Translate table.  For example, enter a fixed effective date or prompt the user for a fixed effective date.
23. Connected Query:  You may find Connected Query to be a useful solution when writing a report.  It enables you to create a single XML file based on an existing set of queries with parent-child relationships.  Among the reasons to use Connected Query are these:
    • Simplify the writing of reports
    • Replace Crystal reports (that use sub-reports) with BI Publisher reports
    • Supply data that needs to be in XML format / Provide a data source for a BI Publisher report
    • Facilitate Left Outer Joins and make Unions easier