Session Description

Advanced Query will assist in tips for complex queries and give you an understanding of Aeries Table Relations. This session will also cover the “CHANGE” button and command, along with the use of Math Operations within query.

Session Content

• Tips for complex queries
• Use of the Database Table Information Report or Tables and Fields Descriptions
• Building Complex Query Statements
• Math Operations within Query
• Using Dates and Null vs Blank in a Query
• Change Button versus Change Command 
• Exporting from Aeries to a File
• Troubleshooting of Query Errors

Query Tips

• Ask why the query is needed and what the data will be used for.
• Identify the fields you wish to include and the tables from which they come.
• Using a KEEP or SKIP may make the final outcome easier or more accomplishable, but a full use of the IF portion is always best if you wish to save the query as a one-step solution.
• What is the required table order to make the query run with the desired outcome?
• Using sorts can make it easier for those that run it later?
• Do I need to teach someone else how to do this query?  Share the load and make life easier for yourself and more rewarding for others.

The Query Form is Dynamically Helpful in Building Statements

The 5 function areas of a query statement are: 

• Command  [LIST TOTAL KEEP SKIP CHANGE]
• Table(s)
• Field(s)   (optional)
• Sort  [BY]   (optional)
• Conditions  [IF]     (optional)

NOTE:  Not all are required but they are to be in this order if utilized

How do we know what tables to include and the order? 

• Rely on the interactive nature of the Query Form starting with a primary first table.
• Review queries that resemble what you are trying to accomplish.
  • Review queries your district/school already has saved by selecting the LOAD button.
  • Check aeries.com for queries. https://support.aeries.com/support/solutions/articles/14000095910-sample-queries-query-banks
  • Sign up for the Aeries_Talk Listserv. https://groups.yahoo.com/neo/groups/aeries_talk

Sample Queries 

Math Queries

• The “Class Of” can be calculated based off of the student’s current grade:
  LIST STU ID NM GR (( 2023 + 12 - GR ))

• Cumulative credit completed (CC) is being divided by the cumulative credit attempted (CA) and then multiplied (*) by 100 to convert it to a percent:
  LIST STU NM CC CA (( CC / CA * 100 )) IF CA # 0

• Days present are divided by the days enrolled and then multiplied by 100 to calculate the Attendance Percentage
  LIST STU NM DE DA DP (( LEFT (1.0* DP / DE * 100 , 5 ) + "%" )) BY (( DP / DE )) REV IF DE # 0

• Truncate to the right down to the last four digits of the student ID
  LIST STU NM ID (( RIGHT (ID,4) ))

Dates and Null or Blanks in Queries

• List of students active in Special Education
  LIST STU CSE TCH STU.ID STU.NM STU.GR TCH.TE CSE.DI CSE.ED CSE.XD BY STU.NM IF CSE.DI > 0 
  AND CSE.ED # NULLAND CSE.XD = NULL  

 OR use an Extended Field:

LIST STU CSE TCH 

STU.SC STU.ID STU.NM STU.GR 

TCH.TE CSE.DI CSE.ED CSE.XD SPECIALED

BY STU.NM 

IF SPECIALED = "YES"

•  List of students with a Assertive Discipline record during specific dates. Sorted by grade descending and gender ascending.
  LIST STU ADS DSP STU.SN STU.NM STU.GR STU.GN ADS.CD ADS.CD?  DSP.DS? ADS.DT

  BY STU.GR ^ STU.GN IF ADS.CD # " "

  IF ADS.DT >= 12/02/2022 AND ADS.DT <= 12/05/2022

  
  

  
  
• Students with no course requests at the District.

  LIST STU SSS STU.SC STU.SN STU.NM STU.GR STU.CU BY STU.CU IF SSS.SN = NULL

• Students who have a Student Flag. Data is not blank in a field.
  LIST STU FLG STU.NM STU.TL FLG.TC IF FLG.TC # " "
  
   

 Change Queries

• Change Testing Group Name to the student's teacher name.
  CHANGE  STU SSD SSD.TGN TO  TCH.TE  
  
  
• Updating TBD to IFEP in Language Assessment (LAC)
  CHANGE STU LAC LAC.IFD TO LAC.ITD IF STU.LF = “T” AND LAC.ITF = “IFEP” AND LAC.ITD # NULL AND LAC.IFD = NULL

• Change Graduation Fields for 12^thGrade Students. 
  • Change students in 12th grade to a Completion Status of 100 - Graduated, Standard HS Diploma if the Completion Status field has not been populated
    CHANGE STU STU.HSG TO 100 IF GR = 12 AND STU.HSG = " "
  • Change students in 12th grade to have a specific Completion Date if the Completion Date has not been populated
    CHANGE STU DG TO MM/DD/20YY IF GR = 12 AND DG = NULL

• Change to clear Absence Code of “I” to blank for all students in a specific class (section) for Attendance Period 1 on a specific date.
  CHANGE STU SEC ATT ATT.A1 TO " " IF SEC.SE = #### AND ATT.DT = ##/##/20## AND ATT.A1 = I

Other Data Tools:

• Using the Custom Report Builder is a far easier way to output program involvement and other sub groups.
• Analytics is another friendlier way to dig in to data. You can drill down by any other sub group far easier than having to be adept at a query language.
• SQL queries & SQL views used to depict a lot of relational data on one line:

Aeries Query Expansion through SQL views:

https://support.aeries.com/solution/articles/14000092446-aeries-query-expansion-through-sql-views

Link to SQL Query:  https://support.aeries.com/solution/articles/14000082583-dynamic-sql-queries-to-provide-a-rich-one-line-result-of-whatever-unique-programs-and-or-authorizatio

Related Documentation

Aeries Table Relations

Database Table Information Report

Tables and Fields Icon

Database Definitions
Extended Fields

Advanced Query

Advanced Query - Secondary Examples

Query SKIP or KEEP

Query Change

Query Letter

Query Labels

Query Envelopes

Aeries Query Expansion Through SQL Views

Related Videos

Query Math Functions Video

Query Change Button Video