Student Faculty Information System

• An aggregate function performs a computation on a set of values rather than on a single value.  It is a special type of operator that returns a single value based on multiple rows of data.
• When your query includes one or more aggregate functions, PeopleSoft Query collects related rows and displays a single row that summarizes their contents.

• An expression that evaluates to either true or false, and then returns a result that meets the condition that you specify.
• If you add multiple lines of criteria to a query, you must use Boolean operators to specify the way that the system applies each criterion.  (See Boolean Operators)

• AND:  Returns only rows that meet the conditions of all criteria
  • Use the AND operator to view only Japan-based employees who have a capital D in their names.
• AND NOT:  Returns only rows that meet the condition that precedes this operator but yet do not meet the condition that follows this operator.
  • Use the AND NOT operator to view only Japan-based employees except for those who have a capital D in their names.
• OR:  Returns rows that meet any condition in the criteria
  • Use the OR operator to view all Japan-based employees and all employees who have a capital D in their names regardless of location
• OR NOT:  Returns rows that meeting the condition that precedes this operator and rows that do not meet the condition that follows this operator.
  • Use the OR NOT operator if you want to return all Japan-based employees and employees whose names don’t start with D regardless of location.

1. The basic steps involved in building a query are as follows:
   • Determine what table (record) or view you need to extract data from.
   • Select view and/or record component(s).
   • Select fields.
   • Format the query output.
   • Specify selection criteria.
   • Save the query.
   • Run the query.
   • Verify the data.
2. It is a good practice to frequently save the query as you are building it.
3. Build your queries incrementally.  If dealing with multiple criteria, pull them in one at a time, and rerun the query after each criterion is added.  This allows you to control the output and identify whether the query is working correctly before you run it against your entire target population.
4. In conjunction with the previous item, before running the query the first time, be sure that you have enough criteria in place to prevent all records being returned from a table.  Do this by setting temporary criteria to limit the returned results.

• A Cartesian join is a join of every row of one table to every row of another table. This normally happens when no matching join columns are specified. For example, if table A with 100 rows is joined with table B with 1000 rows, a Cartesian join will return 100,000 rows.
• Cartesian Product:  A Cartesian product is defined as all possible combinations of rows in all tables.  If two tables in a join query have no join condition, Oracle returns their Cartesian product. Oracle combines each row of one table with each row of the other.  A Cartesian product always generates many rows and is rarely done on purpose.

• Refines the query by specifying conditions that the retrieved data must meet.
• A criterion specifies which field values should be returned in its results.
• Query uses the paradigm of a funnel to describe how criteria affects the data it examines.
• By applying criteria to records and selecting which fields you want from those records you use Query to find the data you need. That is all the tool does; no more, no less.

•  Contains the  values for each field in a table.
• To identify each data row uniquely, the system uses a key consisting of one or more fields in the table.

• A structured collection of records is known as a database.  A database facilitates an organized storage of data in the form of records.
• The records can be queried in different ways to retrieve information from the database.

• When you start a new query and select an effective-dated record, a new effective date criteria row is created, and an information message appears on the screen.
• By default, the query tool will retrieve current information. This can be changed.

• The Distinct option is checked under Query Properties to eliminate duplicate rows in a query result.

1. Current – The highest effective date is less than or equal to today’s date (system date on server). There can only be one current row
2. History – The effective date is less than the current effective date
3. Future – The effective date is greater than today’s date.

You can use effective date fields in criteria to:
• View all rows, regardless of their effective dates
• View rows that aren’t currently in effect
• View the rows that were effective as of a particular date

• The EFF_STATUS field has two values, Active and Inactive. If you are working with effective dated tables and looking for the current row of information, you may also want to add criteria in the EFF_STATUS field to specify only active rows.

• The table may also include the EFF_SEQ field. The EFF_SEQ allows you to enter multiple data on the same date.

• Expressions are calculations that PeopleSoft Query performs as part of a query.
• An expression can be treated as a field.
• When selected for output, you can change its column heading or sort it.

• Contains the smallest unit of information that you can access
• Each record is defined to contain a certain number of columns or fields.
• These fields will exist for any row in the record, but may or may not actually have a value.
• Certain fields may be designated as keys.

• Functions are unique types of command words, usually one-word commands, which manipulate data items and return a single value which can be determined by various input parameters.
• These functions can often be utilized in Query Expressions created on the Edit Expression Properties page.

• A Wesleyan Function is pre-written text created specifically for inclusion in Query Expressions for Wesleyan users.
• These functions are in addition to numerous standard functions that can be applied to Query Expressions.  They have been developed by ITS programmers to enable the PeopleSoft user to exploit the benefit that Expressions can lend to a query.  

• When on the Criteria page, you use Group Criteria to relate multiple criteria in specific ways that you define using the AND, AND NOT, OR, and OR NOT operators.
• You can also group criteria using parentheses.

• A join combines two or more records.
• There are three basic forms of a join: an inner join, an outer join, and a Cartesian join. The query tool assists you in making the first two types and makes it very difficult to do the last, which is important as the last type of join should never be used.
• A well structured join should be between the key fields of the two records being joined.
• From a theoretical standpoint, there is no limit on the number of joins in a query. In practical usage, queries that involve more than four or five joins may take too long to run and many joins indicates a need to review how you wrote your query.

• A field which uniquely identifies each row of data on a table/record.
• The most common key field in PeopleSoft is an EmplId, but often it needs to be combined with other fields such as effective dates to ensure that all rows are truly unique.

• Information about data.
• Metadata is the information that a database or application stores to describe its own business data.
• Metadata can also describe more complex data relationships.

1. The “In-list” operator – recommended
2. Multiple OR clauses
3. The UNION operator – usually fastest, but not recommended because of increased coding complexity
4. The “like” operator may be appropriate if a large number of values are targeted
5. A subquery is usually the slowest. The subqueries have to be evaluated first, and must be compiled before the parent query runs.

• An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
• In a left outer join, all rows of the first (left) record are present in the result set, even if there are no matches in the joining record.

• For a query to run, the popup blocker should be turned off.
• To find out the status of your popup blocker: Internet Explorer users: click on Tools > Popup Blocker > Turn off Popup Blocker Mozilla Firefox users: click on Tools > Options > Content – remove check mark from the Block Popup Windows check box.
• Many times Yahoo or Google toolbars are added with their own toolbar, make sure it’s turned off before you run the Query
• Note: you may need to hold down the Ctrl key even if your pop-up blocker is turned off when running to EXCEL.

1. Anyone with the proper security can use a public query.
2. Only the person who created a private query can use it.
3. It is important not to make changes to any query you did not create. If you want to make changes to a query, rename and save the query before making any changes.
4. If you create a public query, consider creating a private copy with a unique name for yourself. Therefore, if someone mistakenly changes a public query you created, you still have a copy of the original query.
5. Always save your private version that you created from a public query with a unique name. You might want to use your initials as the last three letters of the query name.
6. When you search for queries from the Query Manager Search page, PeopleSoft automatically lists all private queries you created. Only you will see these. Public queries are listed after private queries.
7. If you run a public query and do not receive results, you may not have authorization to some of the data used in that query. 

• Query Viewer is a read-only version of Query Manager.  It enables you to:

• Search for a query using the basic or advanced search functions
• Run a query (which displays results in a new browser window)
• Download the results to a Microsoft Excel spreadsheet
• Print a query

• A record (in PeopleSoft Query) is collection of rows that holds a common set of data.
• Each row is uniquely identified by a unique key which can be made up of one or more fields.
• There is no correlation between most screens on the user side of PeopleSoft and one specific record in the database.
• When someone speaks of reporting tables or views, they are referring to specific forms of records, but Query refers to them as records.

• A relational database is one that contains multiple tables of data that relate to each other through special key fields.
• Relational databases are far more flexible (though harder to design and maintain) than what are known as flat file databases, which contain a single table of data.
• Data can be accessed or organized in different ways without the need to make accompanying changes to the tables.

• Relational databases are created using a special computer language, structured query language (SQL), which is the standard for database interoperability.  SQL is the foundation for all of the popular database applications available today, from Access to Oracle.
• SQL accesses and manipulates data in databases.
• Query Manager generates SQL (Structured Query Language) based on your query definition.
• You don’t have to know SQL to create a query in PeopleSoft.
• On the View SQL page  you can examine the underlying SQL code that Query Manager generates based on your query definition. However, you cannot modify SQL on this page.

• If you have a long text field (like a comments field) in your query results, and you use the DISTINCT option on the query or you are grouping by the long text field, you will receive an Oracle error (which begins “SQL error. Stmt #: 5682 Error Position: 84 Return: 997 – ORA-00997: illegal use of LONG datatype..”).
• To work around this, remove the DISTINCT if applicable.  (Click on the link for more information.)

1. A subquery is a query within a query.
2. Subqueries enable you to compare the value for a field in the outer query to the results of the inner query.
3. A subquery can retrieve only one data field from a single table. The subquery can contain a table join. However, criteria can be set up on many fields without using the results as a field.
4. The results of the subquery are not displayed.  The query results are limited by the results of the subquery.
5. Single value subqueries use the condition types of exists or does not exist, in other words, the results are true or false.
6. Using exists or does not exist require a link between a field in the subquery and the outer query.  You must set up table join criteria.
7. Subqueries can also be set up using “in list” or “not in list”.  These are multiple value subqueries.  If you set up a “exists” or “not exists” and get the error message, not a single value subquery, change the condition type to one of the list formats.

1. A union query is two select statements brought together in the same query, basically two queries running simultaneously.
2. Use a union query to combine records that have no fields in common to retrieve similar values.
3. Unions are especially valuable to avoid situations where a record join will produce inaccurate results.  For example, when joining the employee and position tables, only those positions which are filled will be returned.
4. If one simple rule is followed, union queries will always execute properly.  The two portions of the query must have the same number of fields, in the same order like to like (field type and length).  The field type must be exact and length similar.  To achieve this, use literal expressions as placeholders.
5. The “balancing” literals can be generic, character is ‘’(two single apostrophe marks), number is 0 (zero); or you can designate a word, between the two apostrophe marks (‘vacant’) or integer (‘2’) to clarify the report content.
6. The table with the largest field sizes must be chosen as the top level of the query.
7. Unions retrieve unique rows only.  If both select statements retrieve the same row, the row will only appear once in the final output.
8. You cannot use the long or short translate values in union queries.

• A view is a logical representation of data and may consist of data from multiple tables depending on how the record was defined.  Views generally end with the letters VW.

• The table you’re querying may include fields that use the Translate table. If so, the field itself contains a short code of some kind, for which the Translate table provides a set of corresponding values. For example, if the table includes an EFF_STATUS field, the value is A or I, which the Translate table translates into Active and Inactive. If a field has values on the Translate table, a letter appears in the XLAT column for that field.
• In your query results, you might want to display the translated value rather than the code (for example, Active instead of A). To instruct PeopleSoft Query to make this substitution, specify L as the 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, PeopleSoft Query uses the value in the EFFDT field for a row. That is, for each row the query returns, PeopleSoft Query uses the Translate table values that were active as of that row’s effective date.
• If neither of these effective date options are what you want, you have two more options:
  • If the table you’re querying includes another date field, you can 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.