Outline
Query FAQs
What is Query?
PeopleSoft Query is a tool that you use to retrieve selected data from the database. With PeopleSoft Query, you can extract the precise data you want using visual representations of your PeopleSoft database, without having to write SQL statements. The queries can be as simple or as complex as necessary and they can be one-time ad hoc queries or queries you’ll use repeatedly.
A large portion of the SFIS Blog is devoted to Query. For specific instructions, look at the following:
Part I – Basic Query Concepts and Query Viewer
Query Is:
- A method to examine and report on data
- Helpful to find trends/patterns in data
- An excellent way to get raw data for further analysis
- Very literal in how it processes your requests and what it returns
Query Is Not:
- A data analysis tool
- Computer programming
- Well suited for summaries or encapsulated looks at data
- Forgiving of a minor typo or logic error
How do I know which records/tables to use when creating a query?
You will use certain tables over and over again and you will know the table names without needing to do research. There will be times when you are familiar with the data from using PeopleSoft Pages, but you may not know what records the data exist in behind those pages. Most of the records in the database have an abbreviated name, which will give you some clues as to their contents.
There are a number of methods to ascertain which are the applicable records/tables or views to use in a query.
-
One way to narrow down which records you may want to employ is to look at queries already created within your department. You can work with your colleagues on defining what has already been done that may be applicable.
-
A second approach is to look at the lists of Wesleyan’s commonly used records. Note that users have varying degrees of access to tables within PeopleSoft
-
A third method – allied with the first – is to observe the records underlying an existing query. This has the added benefit of showing you how records are joined.
-
Fourth is the use of the Advanced Search on the Find an Existing Record page to search by various identifiers such as a few words in the description or a field name, if known.
I am having trouble downloading to Excel. What should I do next?
If you are having difficulties downloading a query to Excel, take a look at Running a Query to Excel below to see some suggestions.
Can I improve Query performance?
For information on query performance issues, take a look at the section below entitled Query Performance.
I am receiving error messages when working with Query. What do they mean?
Navigate to the section below on Query Error Messages.
What are some troubleshooting tips for working with Connected Query?
- Check if member queries of affected CQ has unused prompts. If so, remove unused prompts from member queries and retest CQ.
- Make sure that parent query returns data.
- In order to figure out which field of which query causes an error, try removing related fields one by one in order to see if/when problem disappears.
- Click here to see more information on working with Connected Query.
In Communication Generation I am having problems working with the Data Source Query. What are some areas to look at?
- The query used with the Communication Data Source, which should be used to select the additional data that is needed for the letter or e-mail, should include a prompt for each of the variable data items relating to the administrative function. Your query needs to include a prompt for each.
- There may be a problem with the SQL relating to the query being used as part of the Data Source (e.g. missing core record relating to the administrative function, prompt for EMPLID needs the Unique Prompt Name changed to PERSON_ID, prompts needed for variable data)
- The Template may be corrupt.
- You will find extensive troubleshooting documentation in the CommGen pages. In particular, reference this document: Communication Generation Troubleshooting Issues and Solutions
Running a Query to Excel
- If you are having difficulties running a query to Excel, here are some things to check out:
- The file type settings in Windows Explorer
- The popup blocker in your browser.
- Since problems with running queries to Excel are often browser related, start with this article, updated October 2016: Configuring Web Browser Settings. This reviews how to configure Internet Explorer, Firefox and Chrome to work with PeopleSoft regarding popup settings and the browser’s cache. You will find this and other information on the Web Browsers and PeopleSoft page.
- The above article was adapted from a similar document on another website. The original article describes working with an older version of IE, but it does display a good example of what happens when you don’t make the changes to Popup: Pop-Up_OpenAttachment_Blocker
- If you would like to run a Query to Excel using Query Viewer, navigate to this page: Query Viewer – View Results and Run to Excel
- For information on using Excel’s pivot table, click here: Query to Excel – Pivot Table
Query Error Messages
When working with Query, 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 one of theseOracle errors.
SQL error. Stmt #: 5682 Error Position: 84 Return: 997 – ORA-00997: illegal use of LONG datatype…
OR
Error in running query because of SQL Error, Code=932, Message=ORA-00932: inconsistent datatypes: expected – got CLOB (50,380)
(From PeopleBooks) In Oracle databases, PeopleSoft LongCharacter fields use the CLOB datatype when their length definition exceeds 1333. Since CLOBS and other binary datatypes are not valid columns for all operators, all queries including columns with LongCharacter field length definition that exceeds 1333 should not include the DISTINCT operator.
Work Arounds:
- To work around this, remove the DISTINCT if applicable.
- If you want to attempt to retain the DISTINCT:
- You can create an Expression that will add a new Character field to substitute for the Text field.
- First determine how many characters there are in the field with an Expression: MAX(LENGTH(A.SAMPLEFIELD))
- Then use the TO_CHAR function in an Expression to add a new character field with the maximum number of characters.
- Be sure to check Use as Field
- Once you’ve created the Expression, remove the Text field from the query.
- Examples:
- (TO_CHAR(SUBSTR(A.SAMPLEFIELD,55,950)))
- TO_CHAR(A.COMM_COMMENTS)
Query Performance
- Troubleshooting Query Performance – Issues and Solutions: For information on query performance issues, take a look at this article. Included are some typical situations and suggested strategies on how to fine-tune query performance as to response time through an understanding of the foundations of the query process. If you have a query that is performing slowly, timing out or returning an unmanageable number of records, here are some items to look at.
- Run Time
- Columns / Fields
- PS Query and Security
- Joins
- Tables / Records
- Views
- Criteria
- PeopleSoft Query – Part III – Advanced Concepts – Lesson 11: As mentioned in the above article, another source of information is Part III of the PS Query instruction module where you’ll find 11 approaches that can aid you in the smooth execution of a query.
- Run Time
- Columns
- Preventing a Runaway Query
- Joins
- Duplicates
- Views
- Table Ordering
- PS Query and Security
- Criteria – Constants and Prompts
- Multiple Criteria Values
- Return a Limited Number of Rows