Selecting Records by Criteria
Criteria to select only certain records for the query results is added to the end of the query statement. The IF command along with the Comparison symbols are used to indicate a query condition.
Query conditions can take on multiple forms.
Using the contents of a field as the criteria
- Medical History using a populated code as the criteria
LIST STU MHS STU.NM MHS.CD? BY MHS.CD IF MHS.CD > 0
Using red flags as the criteria
- Assertive Discipline using the red flag as the criteria
LIST STU ADS FLG STU.GR STU.NM ADS.CD ADS.CD? ADS.DT ADS.DS IF FLG.TC = ADS
- Flags sorted by table
LIST STU FLG STU.NM STU.GR FLG.TC FLG.CO BY FLG.TC IF FLG.SN > 0
- Flags listed for a specific table
LIST STU FLG STU.NM STU.GR FLG.TC FLG.CO IF FLG.TC = MED
- Flags for a specific student
LIST STU FLG STU.NM STU.GR FLG.TC FLG.CO IF STU.NM : ABBOTT
Using “Null” or ""
- Using a populated user field as the criteria
LIST STU NM PG AD CY ZC TL MW FW IF U7 # " "
Working with dates
- The "?" after BD will produce the age of the student, whereas the ".MONTH" after BD will return only students born in December.
LIST STU TCH TCH.TE STU.NM STU.BD STU.BD? IF STU.BD.MONTH = 12
- Students who have not had the 1st polio shot – no date is entered
LIST STU IMM STU.SN STU.NM IF IMM.P1 = NULL
Using Connectives “AND” and “OR”
- When using AND all records must meet all IF conditions.
LIST STU TST CTL CTL.NM TST.SN STU.NM TST.ID TST.RS TST.SS TST.PL TST.TD BY STU.NM TST.PT IF TST.ID : CST AND TST.TD.YEAR = 2012
- When using an OR the records must meet either condition.
LIST STU TCH TCH.TE STU.NM IF STU.CU = 9 OR STU.GR = 3
- When combining AND with multiple OR parts in an IF STATEMENT, encase the OR portion in ( ) and place a space before and after each parenthesis.
LIST STU TCH TCH.TE STU.NM IF ( STU.CU = 9 OR TCH.TE : SMITH ) AND STU.GR = 5
NOTE: The "OR" portion of the condition is placed inside of ( ) so as to be considered one condition when the "AND" portion is added.
Various math operations can be performed in Query. For example, in the query below the example is using the fee amount charged (AM) and subtracting the amount paid (PD) and then only displaying the information if the remaining amount is greater than zero.
As usual, there is a space between each command, table, field, operation or condition. You can add, multiply, divide or subtract within the start and stop math symbols.
Math operations can also be performed to truncate a field or “cut off” characters or numbers from left or right. The following query example will truncate to the left all characters up to the first character of a student’s first name.
Math operations can also concatenate or combine fields and strings. For example, the student’s last name and first name can be joined. In the example below the students last name is joined with the first name and has a comma inserted between them. Take note to the first selection after STU, which is NM. Notice that NM is nowhere listed in the STU Fields. Aeries Query NM is a programmed concatenation of a student’s last name, plus comma, space and last name.
Math operations can also perform operations, concatenate and truncate within the same query. The math operation below has calculated from days present (DP) and days enrolled (DE) the actual percentage that a student has been present.
Changing the 100 to 100.0 in the above query will allow the query to display the calculated value to the hundredths place.