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.DTBY 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 12thGrade 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 students in 12th grade to a Completion Status of 100 - Graduated, Standard HS Diploma if the Completion Status field has not been populated
- 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
Database Table Information Report
Database Definitions
Extended Fields
Advanced Query - Secondary Examples
Aeries Query Expansion Through SQL Views