Blogs

Did you know (DYK) - July 2022

Within the Office of Student Financial Aid Services (OSFAS) at the University of Connecticut, it was observed that despite very similar criteria, communications often necessitated multiple 3C queries to generate the population selection.  In many cases the only difference would be a separate letter code required due to header/footers and/of information for specific groups of students.   

OSFAS endeavored to reduce such redundancies, thereby alleviating 3C query maintenance by focusing on prompt driven queries for run controls to supplant the need for multiple 3C queries in many cases.  Below are two of the methods the office has employed in its 3C queries along with abstract examples for you to try in your environment.

Comma separated prompts

While such function can be achieved by a series of ‘OR’ statements and optional prompts, our office decided a singular prompt with a comma separated list would be more prudent for setting Run Controls.

This was achieved by creating a criteria using the INSTR() function.  Below is a simplified example using the AID_YEAR_TBL.  We will create a prompt that will allow the user to obtain a listing of details for each AID_YEAR entered, separated by a comma.

1. Create a prompt for the field you wish to retrieve multiple values from, in this example the AID_YEAR field.
2. The standard for most fields will be a Type of Character and Format of Upper. *Note*, the length of the prompt is paramount as you need enough character spaces to contain all your separated values, it is ok to give a very large value.  For this example, we selected 250 characters.
3. Create a criteria, for the Expression 1 Type select Expression and proceed to create a New Expression.
4. Within the expression, choose Character with a Length of 1 and enter 0 within the Expression Text.
5. For the condition Type, select Not Equal To and Expression for the Expression 2 Type.
6. From here, we will utilize the prompt created in Step 1 and the Field we are using the list for (A.AID_YEAR) in concert with the INSTR() function. instr(',' || :1 || ',' , ',' || A.AID_YEAR || ',')
7. Completed example:
8. Test of the example using 2010,2013,2019,2022 in the prompt:
   

   
9. SQL for the Example

SELECT A.INSTITUTION

        ,A.AID_YEAR

        ,A.DESCR

        ,A.DESCRSHORT

        ,TO_CHAR(A.ACAD_YEAR_START, 'YYYY-MM-DD')

        ,TO_CHAR(A.ACAD_YEAR_END, 'YYYY-MM-DD')

FROM SYSADM.PS_AID_YEAR_TBL A

WHERE (0 <> instr(',' || :1 || ',', ',' || A.AID_YEAR || ','))

• Use cases:
  1. Our office has used this method with commgen communications that are driven by career. Instead of separate queries covering various careers, we use one query and have the Career as prompt, using a list for those careers with matching letter templates.
  2. In limited cases, we have opened this method to informational queries where the end user might want to enter in a small listing of Emplids.

Caveat: There is a limit to the amount of comma separated values entered in the prompt before the query returns an error.  No matter the size of the prompt, in our testing we discovered with our 7 digit Emplids that the prompt limited us to 53 entered in a comma separated manner.

Filter/Criteria Selection by Prompt

Another method our office uses to limit duplicative queries is a prompt to include certain selection (where) criteria in our query.  In this case we group and nest a set of criteria by ‘OR’ statements, only taking the filtering criteria where the prompt value matches.

Below is a simplified example using query with a join between the AID_YEAR_TBL and AID_YR_CAR_TERM tables.  The static criteria for the query is the Career code of UGRD.  We will prompt for an aid year then be prompted to select either the Fall, Spring or Summer term information by entering FAL, SPR, or SUM. 

*Note* for this example I created an expression to match the corresponding term code based on the AID_YEAR entered by prompt.  This is specific to our environment and you will either want to hardcode yours or do something similar for the exercise.

1. Tables used in the example:
   
2. Create the prompt that will for the variable criteria selection, in the example we are prompting the user to enter either FAL, SPR, or SUM
   
3. Set criteria to match the prompt value entered, group the criteria in a set of ‘OR’ statements
   
4. Finalized Query
   
5. Testing Results:
   

   

   

   
   

   

   
6. Use Cases:
   1. A common example for our office was the case of communications for scholarships that had a regular and STEM offering. The base criteria for eligibility would be the same for both offerings, however, a prompt was provided to include or omit the additional criteria for the STEM component.  This enabled the office to utilize one 3C query for multiple letters.
7. SQL: