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
  • 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


Query Tips

  • 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? 


Sample Queries

 

 

Math Queries

 

  • 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) ))

 

 

Change Queries

 

Updating TBD to IFEP in Language Assessment (LAC)

  1. 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. 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 = " "

  1. 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.

  1. 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:

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


Here's a link to that 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

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