How to View, Run and Create a Query

Section I: Navigating the Query Tool

After logging in to MaineStreet and the Human Resources Database (Enterprise Applications), navigate to Reporting Tools > Query.

Query Viewer allows you to select a query and run it.  This article will be focused on the options under Query Manager.

Click Query Manager and you’ll notice that the default is Find an Existing Query – Basic Search which is very limited on how to search for queries.  Also note that you have the choice to Create New Query.   The Advanced Search gives you many more option in finding queries.  You can look for queries that have a certain word in their name by changing the Query Name drop-down box to “contains” or look for queries that use a certain field or record, which will become more useful as you write more of your own queries. 

Knowing where to look, and how to look, for queries means it will be easy to tell if someone has already worked on the same query.  You can then re-save and tweak their query to meet your own needs and in a few lucky cases, someone else will have already tackled exactly the same issue.

Run a Query

Run to HTML

From a list of queries, you can click the HTML link to run the query.  The results will display in another tab of the browser.

Run to Excel

There a several options for return data from a query.  You can have it placed directly in to a spreadsheet by clicking on the Excel link, visible in Figure 3.  Any values are brought over as-is, so if you did any calculations or sorting you get the values after the changes have been applied.  The Excel option is available anywhere you run a query.

Run to XML

Not used for HR queries

Schedule

Clicking the Schedule link next to a query name will allow you to schedule a query to run at a specific date or time as well as to set up a recurring run.  Scheduled query results will show in Process Monitor.  Scheduling a query to run is sometimes used to run long-running queries since there is a timeout feature when running via Query Manager.  Be sure your query is written appropriately so it doesn’t become a “runaway” query.  Runaway queries are those queries that run forever and are taxing on the database and may need to be stopped by a database administrator.

On the Add a New Value tab:

1. Private Query:  leave as is
2. Query Name:  leave as is
3. Run Control ID: enter your name or initials
4. click Add
5. Account Description:  copy/paste query name or any other description
6. Click Okay

On the Process Scheduler Request page:

1. Server Name:  leave blank
2. Recurrence:  select as appropriate
3. Time Zone: leave blank
4. Run Date:  select current date
5. Run Time:  defaults from Recurrence
6. Type:
   1. Web to receive results in Process Monitor
   2. Email to receive an email notification with results
7. Format:  select as appropriate (XLS works well for Excel)
8. Distribution:
   1. Email Subject:  add as appropriate
   2. Message Text:  as appropriate
   3. Email Address List:  add other email addresses who should receive the report (you will receive the report automatically so there is no need to input your own email address).
9. Click OK
10. Click OK again

Go to Process Monitor:

1. click Refresh until run status is Success and Distribution Status is Posted
2. click Details
3. View Log/Trace
4. If you selected Web type above, your query results will show here.  If you selected Email, then check your email for query results.

Copy a Query

Never change (and never, ever break or delete) someone else’s query.  There are many great public queries available in Query Manager which have already been written.  Using what you know about how to find data, and searching for queries, it should be easy to find other queries using the same concepts you want to use.  However, you may still need to tweak the query for your purposes which means you should resave (and rename) the query in your own library.  After you’ve saved someone else’s query as your Private query, you can begin to make modifications. 

Use the Save As link at the bottom of query to resave a copy.

When you are editing a query, there is a link called Properties – click that and you’ll see all of the information above as well as the following information:

That tells me that the query was last saved in September of 2015, and I made the change.  All queries have this information and can be a valuable tool if you need to ask a query writer a question.  I also check the update date in case I find I have a bunch of queries with similar names but can’t remember which one I used most recently.

Copy to User

Now using save as is a great way to get queries into your own library, but what if you want to copy a private query to someone else who can’t edit their own queries?  You can either make the query public or you can copy the query to their library.

First you need to find the query in the Query Manager:

Note the action drop down box which gives you several choices.  After checking the checkbox(es) next to the queries you want to copy and select Copy to User, then click Go.  You will be prompted to enter a User ID which is the same as an EmplID.

Once the query is copied it will appear in their queries to run.  You will need to recopy the queries if you make any future updates to the query. 

Rename a Query

To rename a query, select the query you want to rename and choose the Rename Selected  option.  The screen looks like this:

 Folders

Queries can be saved in folders for easy organization.  After selecting the Move to Folder option you will be presented with the option of using an existing folder, or creating a new folder.

If I am searching for queries, I see more than 300 and Query will only lists the first 300 queries in the system.

Changing my Folder View to just look at the Test folder I created in Figure 9 I can easily find my query.

Figure 12, Query in a Folder

Add to Favorites

When you move queries to a list of favorites, you will be greeted with this list of queries as soon as you enter the query manager.

 Figure 13, My Favorite Querie

Delete a Query

Don’t delete Public queries unless they are your own. 

Section II: Basic Query Concepts

Record (aka Table)

A record is collection of rows that holds a common set of data.  Each record has certain fields defined as keys to ensure that each row is unique.  There is not usually a correlation between most screens on the user side of PeopleSoft and a specific record in the database; most screens are comprised of multiple records. When someone speaks of reporting tables or views, they are referring to specific forms of records, but Query refers to them all as just records.

Field

Each record is defined to contain a certain number of columns or fields of data.  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.

Keys

A field designated as a key means it helps define what a unique row is in the 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 or EmplRcds to ensure that all rows are truly unique.

Join

A join combines two or more records. The Query tool assists you in making joins.  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.

Criteria

A criteria specifies which field values should be returned in its results. Query uses the metaphor of a funnel to describe how criteria affects the data it examines.

Result Set

The result set contains the row(s) of data returned by your query.  It will show the fields you selected after applying any criteria you may have added to the query.  Result sets can be empty if no data matches your criteria.

By applying criteria to records and selecting which fields you want from those records you use Query to produce a result set.  That is all the tool does; no more, no less.

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
• A computer programming language
• Well suited for summaries or encapsulated looks at data
• Forgiving of minor tpyos or logic errors

Section III: Writing a Sample Query

Step One: Planning Your Query

In our sample query, we’ll proceed as if we were asked to produce a list of employees for a department with the following information: Employee ID, Name, Job Code, Regular/Temp Status, Employee Class, FTE, and Employment Status.

Planning can be simple, you may just need to determine which fields you need and which records those fields are in.  For more complicated queries, you may need to decide which tact to take beforehand.  A query asking for all vacation time taken by employees in the engineering college might be better approached by finding all of the engineering employees and then their vacation time, a query with a wider scope might be better approached by first finding all reported vacation time and then finding the employees that match that criteria. 

There are basic questions that should be answered before writing queries:

What are Active employees?  Employees might be on leave, on sabbatical, laid off or retrenched.  Should they be counted? 

Note about “Active” employees.   There are 3 different statuses in the Job record:

1. HR_STATUS = Active or Inactive
2. EMPL_STATUS – Payroll Status = Active, Deceased, Leave of Absence, Leave With Pay, Retired, Suspended, Terminated and Terminated With Pay.  The following payroll statuses are not currently being used:  Terminated with Pension Pay Out, Short Work Break and Retired – Pension Administration
3. BEN_STATUS – Benefits Status = Active, Deceased, Leave With Benefits, Retired and Terminated.  The following benefit status are not currently being used:  Leave of Absence, Retired With Benefits, Suspended, Terminated With Benefits.

Should you count temps? Students? Graduate assistants? Benefits-eligible?

What constitutes your faculty? Coaches? PATFA? Non-represented part-time?

When you are asked for numbers do they want headcounts or FTE?

The PATFA issue:

• Some are temporary, some are regular
• The all have a union code, however, keep in mind there are some non-represented, part-time, temporary faculty (not PATFA)
• They are Active for six semester after having last taught
• They are faculty

There are many more issues depending on the data you are looking at.  Discuss with other experienced query writers on how they want to cover these issues and adhere to standards so data retains some consistency from one query to another.

Some basic guidelines to consider:

• Use as few records as possible to access your data
• Don’t over specify your criteria or your result set will be too narrow – consider all of the possibilities
• Not enough criteria will leave you with too much data
• Don’t return fields that you don t need
• Don’t trust Query – always look at results with a critical eye.  Are the results reasonable? Do they make (common) sense?
• Will your audience understand your data or do you need to massage the headings and field values to be human friendly?
• After finding the data you need through trial-and-error don’t be afraid to start over with a firm plan that allows you to create a simpler, more understandable query
• Don’t try to run queries in the 2:30–4:30 AM timeslot (approximately); data is unpredictable as records are repopulated and views and flat files are regenerated.

Step Two: Finding your Data

Commonly Used Records

This list includes some of the more commonly used records for queries:

The most commonly queried record is named job which contains most of the fields you see on the Job Data page.

Flat Files

Records with names beginning with um_f_ were especially created to make querying easier.  They combine many of the most commonly used fields into one table, and in many cases even includes the translation value in separate fields in the record.  The term “flat file” is used because the data has been flattened to show only current data and to combine data from multiple records without any joins.  Think of the flat file as the result set from an enormous query which you can use in your own queries.

To start our query, click Create New Query in the Query Manager as shown in Figure 2

Figure 14, Selecting a Record

Upon starting a new query, the tool instantly asks you to pick a record from which to start.  For most queries, you will want to start with a record that will give you as many of the fields as you need, or holds the fields that will act as the majority of the criteria.  Fields do not need to be in the result set in order to be used as criteria.

Figure 15, Selecting Your Record

In this case, we do want the job record, so click Add Record to use it in your query.  If you’re trying to find a specific piece of data and do not know if the record has what you need,  Show Fields  (Figure 17) will list all of the fields in the query.  Also note the Advanced Search link, you can use that to find records which contain a specific field.

Figure 16, Effective Date Warning

Whenever you use a record that has effective dating, you will get this warning.  What is important to know when you see this message is that the database will automatically give you the current (no historical and no future) row for the criteria.  If you remove the effective dating criteria, this query will return every job row ever created for the employees you select.  We will look at the criteria later and examine its effect and drawbacks.  For now, simply click ok.

Step Three: Selecting Your Fields

Figure 17, Selecting Fields from the Query Tab

For simple queries, you can actually do almost everything you need right from the Query tab.  Simply put a checkmark in the box next to the fields you want, and remove the check field for any fields you found you did not need in the query.  If you don t mind a lot of data you can even click the button to check all of the fields in a record.  But be careful, there are limits on the amount of data you can return in one query. You can even remove the entire record by clicking the minus sign all the way to the right of the record name.

Pressing the icon of an open folder before the record name will collapse the record view if you want to hide the fields.  The most useful on this screen is the little box up above the records labeled “A-Z”.  Press that and it will put the fieldnames in alphabetical order making it easier to find the specific fields you need for your query.   Another useful link is Find which allows you to search the record for a particular word or phrase.

For our query, you’ll need to find the following fields in the job record:

• EMPLID
• DEPTID
• JOBCODE
• EMPL_CLASS
• EMPL_STATUS
• FTE
• REG_TEMP

Joins

The purpose of a join is to bring together data from two different records.  There are hundreds, and very possibly thousands of tables in MaineStreet – joins allow us to bring that data together into one result set and make our queries more useful.  Joins are always between two records, but multiple joins can bring multiple records together.

Inner (Standard) Join

An inner join is the most basic form of join and what you will use in most cases.  Basically an inner join says “I want all of the data in the left record which has a corresponding entry in the right record.” That means it will only return data if both tables can form a join, which is usually what you want.

Outer Join

An outer join allows for the situation where you are saying “I want all of the data in the left record, and if it is available, any corresponding data in the right record.”  The difference is crucial – imagine if you are looking for employees in a department and join faculty tenure data.  If you do an inner join, you will only get employees who have both types of information.  An outer join will produce a list of all the employees with any tenure data for the employees who have it.  Those without tenure data will have blanks in any fields you select from the tenure record.

Subquery Joins

Subqueries will be covered later, but many subqueries also use a form of a join in order to bring data together in different parts of a query.

Note:  It may be very useful to join a record to itself to produce two copies of the record.

The employee’s name is stored in a separate record.  For our purposes, we will use the record person_name to get the employee s name.

Navigate to the tab labeled Records and search for this record and click on Show Fields

Figure 18, Show Fields

Click Return and Join Record

Figure 19, Creating a Join

When you add the record, it asks you how you want to make the join.  You can either do a standard (inner) join or an outer join.  It is also asking you which record to join to.  We want a standard join, so you simply need to click on the job link.

Figure 20, Join Criteria

After selecting the record to join, it will then ask you for the fields to join.  At this point it might be useful to note the aliases, or record indicators.  If you look closely at Figure 16, you ll notice that job was assigned an alias of a. What the screen above is telling you, is that it will join two records: job, which it will call a, and person_name which it prefers to call b, and will perform the join by making sure the emplid in both records are equal.  That is exactly what we want, so clicking the button labeled Add Criteria will allow us to select fields from person_name.  When you select a name field (from the many choices!) use name_PSFORMAT for our example.

Tip: Be careful when doing joins, sometimes Query is too helpful and tries to put too many fields in the join criteria.  You always need at least one piece of join criteria, but too many (say on fields we don t use in our implementation of PeopleSoft) will quickly reduce your query to no data being returned.

Primary versus Secondary Jobs

Job records oftentimes include more than one row per employee since employees may have more than one Job.  Each job is identified by Employee Record (EMPL_RCD) 0, 1, 2, etc. Most of the time ER0 is the primary job but not always.  There are couple of ways to identify Primary versus Secondary Job records.

The JOB_INDICATOR field in the Job record is for reporting purposes only and identifies Primary versus Secondary jobs.

The record PRIMARY_JOBS contains data used to identify multiple and primary jobs used for benefits purposes.  The information in this table is generally more up-to-date than the JOB_INDICATOR field since it is maintained for accurate benefits data.

Step Four: Adding Criteria

Figure 21, Fields

Go to the Fields tab to add the first criteria to our query.  The little funnels allow us to add criteria to a specific field.  Note that we can only pick the fields we previously selected here.  If we wanted to add criteria based on fields that we do not want displayed, we should return to the Query tab and use the funnels there.

Figure 22, Criteria Properties

The Criteria screen presents us with many choices.  Basically we need to define specifically which records we wish to see from the thousands of records available.   

Criteria Conditions

Equal to/Not equal to

The most basic condition; these check for equality.  For numbers the values are compared to any available decimals, e.g. 10.00000001 is not equal to 10, and any text must match in length and case.

Between/Not between

Perfect for date ranges, and in some cases when looking at financial transactions to find a range of activity between two levels.  Note that between includes the values you use.  Using between and looking for job rows dated between 1/1/2015 and 12/31/2015 will find entries on those dates as well as those between them.

Greater than/Less than

Ideal for finding large payments, or transactions less than zero.  Asking for dates greater than 1/1/2015 will not find anything on that day, only starting 1/2/2015 and later.

Not greater than/Not less than

Not greater than means “less than or equal to” and not less than means “greater than or equal to.”

Is null/Is not null

Fields can be blank, empty or null -- this lets you find them or exclude them. 

Data is not created equal, and that is never more apparent than when you are looking at (or for) nothing.  In database terms, an empty field, a field with the value of zero or a field whose value does not exist (null) are all different things.  To a human they all indicate absence of data, but to a database they all carry their own nuances.

Text (character) fields can either be set with a value or they can be empty.  To find text fields with no values, say missing title fields, you will need criteria looking for an empty string.  An empty string is represented by ‘ ’ when you review the criteria. To enter an empty string simply use the Expression 2 section blank and use whatever condition is appropriate.

Number fields always have a value, and should be stored as zero if no value is available.  As mentioned in the discussion in Expressions, some fields that happen to only use number may actually be strings so to test those fields you’ll need to look for empty strings. 

Dates either exist or do not exist, unlike numbers there is no default date and no way to logically convey a date that is empty.  So you need to use the conditions that check for null values in order to find blank date fields.

In list/Not in list

Allows you to pick among a list of options.

Like/Not Like

Like allows us to match strings based on partial matching.  For example, using like and O% for our constant criteria will match every value that begins with an O  – that would be perfect to find every department on the Orono campus. 

Exists/Does not exist

This bit of criteria can only be used with subqueries, basically it asks if X exists (or doesnt) in another query.

In tree/Not in tree

      Not used with Human Resources data.

After setting our condition, which will be equal to for our example, we can set Expression 2.   Make sure that your expression type is set to Constant and enter department code (SFAC) in the box.

While our original specification did not specify one way or the other, it is probably safe to assume that we only want active employees.  This gives us a chance to use another criteria type.   Add criteria for the empl_status field and pick the condition type in list.  You can’t type your Expression 2 here, you have to add your items from a list.  Click the magnifying glass to get your choices.

When you go to select your choices, Query normally will give you a list of possible values.  In this case it also provides the long and short translations of the fields’ values.  Click Add Value for any values you want to include in your query. 

Figure 23, Sample List Criteria

When you’re done selecting values, the criteria should look like Figure 22.

Effective Dating

Successful query writing depends on knowing how the data is organized and stored in the database.  Effective dating may be the most important concept because it is the overriding factor in what you get for data.  For most queries, you will want the most current data so letting Query set the effective dates (or using the flat file) is entirely appropriate.  However, at times, effective dating can get in the way of allowing you to get to the data you need.

When you add a record with an effective date, the following criteria is added:

Figure 24, Effective Dating

Effective dating has its own class of condition type, and in this case it is asking for the row with the last effective date less than or equal to the current date (i.e. today) and the last sequence number for that date.  It excludes future dated rows.  It is entirely possible to ask for rows that were between a date range, on an exact date, or remove the effect dating criteria entirely.

You might want to look at a range of dates to find all of the new student hires done in a month – looking at the current row may only give you rows with minor data corrections but not the actual hire row.  You might want to review across-the-board actions and only look at rows for a certain date, but almost instantly the latest rows will be something else as the result of ongoing data maintenance.  Luckily it is as easy to change effective dating criteria as with any other criteria, but you need to understand the ramifications. 

Removing the criteria completely would have found every row generated for every employee in our department listing example.  Even looking at a range of dates, it is difficult to pick out the latest row (aggregates won’t work) so the best you may do is to get one row for most individuals and 2 or more rows for some of your rows.

You also have to consider effective dating when joining tables.  Over time, values change in the database and while the latest job row for an individual may be from last year, but the table holding job descriptions may have been updated yesterday.  The effect may be minor, but you may end up with data that looks different in your query results then how the same row appears on the user screens if you depend on effective dated translation.

The other major consideration with effective dating is that for technical reasons, a record which has effective dated criteria cannot be used in an outer join.  This stipulation does not apply to the flat file.

Step Five: Save/Run the Query

This step is required prior to running a query.  Click the Run tab and then click the running man icon.

Figure 25, Run Your Query

Fill in the fields to save your query.   The minimum you are required to do is provide a name for the query.  Public queries should begin with UMS and you should do your best to create a description that appropriately describes the query.  During prior database upgrades, queries not starting with UM have been purged.  Also consider this when naming your private queries.  If you are saving a private query that only you can see, you can pick any name you want. 

Figure 26, Saving Your Query

Test the Query

Figure 27, Sample Result Set

Section IV: Refining the Query

Translates

If you return to the Fields tab (Figure 20), you’ll notice that there is a column labeled xlat, this indicates whether or not the database will translate the value of the field into more descriptive text.  An n indicates that translate is available but not being used.  Use the edit button to set the translation options.  Let’s translate REG_TEMP to make our query more readable.

Figure 28, Translate Options

All you need to do is choose if you want the Short or Long translation of a name.  In almost all cases the short name will be enough.  When you return to the Fields tab, you’ll notice that the xlat column now indicates s for a short translation.

Only fields that have a value in the xlat column can be translated.  However, query also can suggest tables for possible translation as well.  If you return to the Query tab and look at the fields in the job record, you’ll notice that certain fields have a recommended join option to the right of the field name.

Figure 29, Suggested Join

In this case, let’s join jobcode to its own data table.  We can add the descr field from that record and use it to provide more information in our query.

Reorder and Sort Fields

One thing you can do to improve the readability of your query is to reorder the fields.  I like to have identifying fields, such as emplid and name_PSFORMAT to the left.  It also makes more sense to have the jobcode close to its own translation field.  To change the order of the fields, navigate to the Fields tab and click the button labeled Reorder/Sort.   Figure 29 shows the order of fields originally selected and the preferred column order in the boxes on the left.   In the right column, you can see the name_PSFORMAT field has been designated as the primary sort and that it will sort Ascending (starting with A.)  The reason we selected the name_psformat field earlier is because it lends itself to sorting.

Figure 30, Reorder columns & Sort Order

Figure 31, Modified Query

After making the changes, you can see that the fields have been reordered, short descriptions are added and the names are now sorted.

Add a Prompt

Figure 32, Adding a Prompt

An important consideration when writing queries is that a query should be reusable.  One of the easiest ways to enhance a query is to add a prompt value that will allow you to change one or more pieces of criteria without having to re-edit the query.  This also allows individuals who cannot edit queries to use your query for multiple situations.  Since our query reports on the employees in a department, we’ll change the query so it asks which department we would like to report on.

Navigate to the Criteria tab and click the Edit button next to the deptid field.  Then change the Expression 2 type from Constant to Prompt.  In this case we want a New Prompt, so we will click that link.

Figure 33, Defining a Prompt

We can leave the criteria as it is.  You might want to change the Heading Text so it gives a more readable prompt to the user.

Save your query and try running it.

Unfortunately, this points out one problem with the prompt system.  It tries to guess at what you are doing and occasionally gets it wrong.  What it is trying to do is make sure that the department code you entered is valid by checking it against the departments listed in dept_tbl (see Figure 31).  Unfortunately, in our system we don’t have access to dept_tbl. You have two options to resolve the problem, you can change the Edit Type  to No Table Edit or you can change the  Prompt Table  to set_dept_vw.  Either will work.  The advantage of the latter option is that it retains the ability to search for valid values.

Most prompts you set up won’t need this extra detail and you will be able to set up a prompt and go.  Prompts are especially powerful when used with date ranges.  Also note that the Format value in the prompt criteria – that tells Query to override what the user enters and make it all capitals.  This prevents you from having to explain to each user the importance of using the proper case with query.

Aggregates

Query can do some calculations for you.  When you are looking at the fields tab, one of the options is to click the Edit button (this is also how you get to the translate options) and create an aggregate calculation on EmplID.

Figure 34, Aggregate Choices

This allows you to apply one of the aggregates to a column in the table.  Note however, that it aggregates values only if every other field is the same.  Consider our earlier example – we are returning a unique EmplId and Name for each row.  Adding an aggregate to FTE, to summarize the total FTE count in the department would do nothing to our query as is – it would only sum the FTE for each individual and produce the exact same results.  Select the Count aggregate option and then Ok.  The message below indicates we’re using EmplID and a key field to join another record (PERSON_NAME).

Figure 35, Warning - Aggregate & Join

If we were to remove the PERSON_NAME record or remove the field, our output would look like this:

Figure 36, Sample Aggregation

Now, consider if we were to remove the criteria to prompt on department.  Then our query would summarize the FTE for every deptid available (depending on security.)  However, you should really consider if this query would really be the best for that task or if it would be better to start over and write a new query.

The other aggregates are also useful:

Sum

Will add together the number of values that match each row returned.  Changing our query to Sum FTEs instead of Count EmplIDs will effectively return a total FTE for each job code in a department.

Average

Will produce an average (arithmetic mean) of the values.

Min/Max

Can be used to find the minimum and maximum values – you might use this to find the lowest and highest paid individuals in a job code or salary grade.

Aggregates are useful, but in some ways are also very limiting.  All of these functions and many more are available in Excel, and it may be useful to bring raw data back and summarize and analyze data there rather than directly in Query.

Note that aggregates should only be used on fields which make sense to be aggregated, there is no concept of a minimum name field or an average date in a database.

Column Headings

When editing fields, there is a third option that allows you to change the heading of a column.  For this example, add the PERSON_NAME record and check the box next to NAME_PSFORMAT.  Click the Fields tab and Reorder the fields so the name is after EmplID.  Click on Edit next to name_psformat as by default it prints the unintuitive LN,FN  designation (Figure 29).

Figure 37, Heading Option

In this case, we have selected the heading should be Text, which means it will use the value we type in the Heading Text field.  This change will affect how the column headings appear both on screen and in any file export.

The usefulness of this feature again is limited, as these changes could as easily be done on a spreadsheet, but if you are running a query routinely and editing your spreadsheet headings every time, then making this one change would save you from that task in the future.

Since we joined another table by EmplID, we must remove the Aggregate that was added in the prior exercise.

Expressions

There are two major uses for expressions:

• Do simple calculations on fields

• Use database features not directly accessible from the Query interface

The second option will not be covered in this course but is one of the most powerful features of query. 

The first option allows you to do simple manipulations of the data to produce new values.  Consider a request to determine what the impact of a 2% raise on all of the salaries in a department would be.  You would first find the field that gives an employee’s salary, and then could create a mathematical expression.  Navigate to the Expressions tab and click Add Expression.

Be mindful of the expression type and length.  The type needs to match your data.  The Length value determines the length of the field, and the Decimals is used for numeric fields when you want a certain number of decimal places in your result.   The decimals subtract from the overall length.  A number defined as 4.2 will hold numbers from 99.99 to -99.99.  You can find how values are stored in the database by looking at the  Fields  tab. (Figure 20)  Although be warned that screen uses a different notation, a 4.2 on the  Fields  tab means 4 places to the left of the decimal and 2 to the right, or a 6.2 expression.  Lastly, just because a field always displays as a number, it may in fact be stored as a character string. 

For our example, click the Add Field  link and find annual_rt in the job record.  Using 8 and 2 for the length and decimals of a number field will be sufficient.  Your expression should like this:

Figure 38, A New Expression

Essentially by creating an expression, you have created your very own field.  As you can see from looking at the updated Expressions tab, the value can be returned (click on  Use as Field ) or you can apply criteria to the value.  By creating this expression, it will perform this equation on the annual rate for every row, and any criteria will be applied accordingly.

Figure 39, The New Expression Ready To Use

Calculated expressions may be better served in a spreadsheet after compiling the raw data, but by using criteria on the expression we also can filter our result set to make it smaller.

Other sample expressions:

Figure 40 Expression – Age at of 1/1 of current year

Figure 41 Expression - Age Categories

Figure 42, Age Categories

Add Subquery (Criteria Exists)

Subqueries are a way to further refine criteria, not to bring in more fields to the query.  The goal is to compare a value in the main query with the result set from the subquery. 

Let’s create criteria for “Exists” subquery with UM_PATFA_DTL to see who taught the Fall 2015 semester per data entered on the Part-Time Faculty page in MaineStreet.

Go to Criteria and click Add Criteria.  Change Condition Type to Exists and then click Define/Edit Subquery.  Join the UM_PATFA_DTL record.  Click the Criteria icon (funnel) next to EmplID to join the records.  Condition Type should be equal to and then select Field from the Expression 2 Type.  Click the Record lookup icon and click EmplID from the A (JOB) record.

Figure 43 Selecting Field for Joining Subquery

Go back to the Query tab to add Criteria but clicking on the Criteria icon for CALENDAR_YR and enter 2015.  Click the Criteria icon for UM_PATFA_TERM and select the Fall semester.

Figure 44 Setting Subquery Criteria

To return to the top level of the query to update that Criteria, click the Subquery/Union Navigation link and then select the Top Level of Query.

Figure 45 Subquery Navigation

Click the Criteria tab and delete the DEPTID = SFAC criteria.  Run query to test.

Figure 46 Subquery Result Set

Group Criteria

Another important concept is the ability to group criteria.  Consider the problem of wanting to final all benefits-eligible employees, and for this purposes you need all active employees who are regular and/or temporary, faculty who do not have N (which means they are eligible) in ELIG_CONFIG2.  Click the Group Criteria button and add parenthesis as shown in the screenshot below.

Figure 47 Group Criteria

Note the logical boxes that indicate how the criteria will be used.  The criteria is looking at current rows that are either Regular OR ELIG_CONFIG2 is not equal to N (not eligible) and if they meet one of those two conditions, they should be in the output.   

Grouping criteria can also be nested in multiple parenthesis.

Section VI:  Conclusion

Additional thoughts:

1. Be mindful of the data that is being shared with folks outside your department and/or expertise.  Consult with others who may have provided the information in the past to compare results.
2. Be mindful of where data is saved.  Confidential data is protected in MaineStreet but once it has been saved in a spreadsheet on a computer, it is your responsibility to protect the data per UMS guidelines.  i.e. The output from all benefits-related queries must be saved in the Benefits folder on the shared G network drive.

 This training and the sample query was developed to show the more common uses of query tool.  Keep in mind that using the flat file would have made this query much easier to write.