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.
- Tips for complex queries
- Advanced understanding of Aeries Tables Relations
- Use of the Database Table Information Report or Tables and Fields Descriptions
- Building Complex Query Statements
- Math Operations within Query
- Change Button versus Change Command
- Change Button with Multiple Tables
- Exporting from Aeries to a File
- Troubleshooting of Query Errors
- 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]
- 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
- The “Class Of” can be calculated based off of the student’s current grade:
LIST STU ID NM GR (( 2015 + 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 ( DP / DE * 100 , 5 ) + "%" )) BY (( DP / DE )) REV IF DE # 0
- Truncate to the right down to the last four digits of the birth date and will only display the birth year
LIST STU NM BD (( RIGHT (BD,4) ))
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 12th Grade Students
- 1. 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 = " "
- 2. 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
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.
Since people still had an interest in having a lot of relational data depicted on one line, I have made SQL queries and SQL views to do just that.
Aeries Query Expansion through SQL views:
Here's a link to that SQL Query...