Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
features_sql_statement_in_use [2017/02/03 09:37]
sped_forms
features_sql_statement_in_use [2017/02/03 09:39] (current)
Line 1: Line 1:
 +====== What is the SQL statement in use all about? ======
 +
 +
 +If you have used SpEd Forms for a while, you may have noticed a textbox at the bottom of most administrative reports entitled "SQL statement in use". This text is generated from the search criteria you select and gives the database precise instructions to find the records that are displayed in your reports. These SQL (Structured Query Language) statements show how the information is stored in SpEd Forms and can be very useful, especially when creating custom reports and importing data.
 +
 +Scroll to the bottom of any report you've run and take note of the "SQL statement in use".
 +<WRAP center round box 67%>
 +{{ :​sql_statement_in_use.gif?​nolink |}}
 +</​WRAP>​
 +
 +===== "SQL statement in use" and it's significance =====
 +
 +Let's take a closer look at a SQL statement that was generated from the administrative "​Student List". This particular statement can be broken down into four parts.
 +
 +<WRAP center round info 60%>
 +The format used is: Table_Name.Field_Name.
 +</​WRAP>​
 +
 +**SELECT:** The first part of the statement "​selects"​ the column or field names that will be used to calculate which records will be displayed and/or which fields will actually be displayed in the results table.
 +<WRAP center round box 25%>
 +{{ :​sql_statement_select.gif?​nolink |}}
 +</​WRAP>​
 +
 +**FROM:** Any tables used in the statement are listed after the "​from"​ keyword. In this example, the "​student"​ table is also "​joined"​ with the "​teams"​ table (whenever the student.student_id is equal to the teams.student_id),​ the "​teammembers"​ table (whenever the teammembers.teammember_id is equal to the student.teammember_id) and the "​iep"​ table (whenever the student.student_id is equal to the iep.student_id).
 +<WRAP center round box 34%>
 +{{ :​sql_statement_from.gif?​nolink |}}
 +</​WRAP>​
 +
 +**WHERE:** Next are the conditions "​where"​ specific criteria is placed into the statement (from the criteria you chose in the search box at the top of the page). In the following example, the Student.Name_FirstName must include the characters "​Some"​ and the Student.Name_LastName must include the characters "​Student"​ and the Student.TeamMember_id must be "​515"​.
 +<WRAP center round box 47%>
 +{{ :​sql_statement_where.gif?​nolink |}}
 +</​WRAP>​
 +
 +**Wildcards:​** The % signs that are used after "​like"​ are wildcards, meaning that any character could also be included in the name. For example, this "​where"​ condition could also include "​someone",​ "​somebody"​ or even "​awesome"​ (if their last name also contained the characters "​student"​ and their teammember_id was equal to "​515"​).
 +
 +**ORDER BY:** Finally, this list will be "​ordered by" the student'​s last name and if more than one have the same last name those will be ordered by first name. SQL statements can then be ended by a "​semi-colon"​.
 +<WRAP center round box 52%>
 +{{ :​sql_statement_order_by.gif?​nolink |}}
 +</​WRAP>​
 +
 +<WRAP center round help 60%>
 +If you need help in deciphering what table and/or field names to use when creating custom reports or importing specific data into your database, please contact the SpEd Forms staff. We're here to help!
 +</​WRAP>​
  
features_sql_statement_in_use.txt · Last modified: 2017/02/03 09:39 (external edit)
CC Attribution-Noncommercial 4.0 International
www.chimeric.de Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0