Create Direct SQL queries
Direct SQL mode queries search for records based on your own self-defined criteria, not the system’s tables, fields, and operators. You use SQL syntax to define the criteria; therefore, you are not limited by interface restrictions.
For example, create a Direct SQL query to search for all students who have more than one absence this year.
To create a Direct SQL query:
- Go to a list page, such as the Student List.
- On the Options menu, click Query. The New Query pop-up appears.
- In the upper-right corner, click the drop-down to select Direct SQL Mode.
- In the box, enter the query in SQL:
-
Note: When running a saved SQL query, the Prompt for Value token can be entered into the SQL in place of a comparison value. This allows users to enter values on the 'Search criteria' pop-up at run time. See Tips for entering the "Prompt for Value" token for more details.
- At the Search based on field, select the records you want to search on to determine which records are included in this query.
- Select the Cache results to increase performance checkbox if you want the system to cache the results of a large query to make scrolling through the pages of results faster. Essentially, this prevents the system from having to re-run the query each time you view another page of results.
-
Click Validate to check the syntax of your statement for possible errors.
- Click Search to use the query, or Save As to save the query for future use.
Tips for entering the "Prompt for Value" token:
- Use brackets and the keyword "prompt" to indicate a prompted value the user will be asked for when the query is used.
- The general format
for a prompt token is:
{ prompt : field ID : default type : default value : label }
- When entering a
prompt value for a text field, enclose the prompt with quotes just
as you would enclose a constant value in quotes.
For example: Where PSN_NAME_FIRST = '{prompt:psnNameFirst}'
- When
entering a prompt for a numeric value, the prompt should not have
quotes. It should appear just as a number would in the query.
For example: Where ACT_PENALTY_TIME '{prompt:actPenaltyTime:default:2}'
- In general, the query should be written as if the user-entered text would be placed exactly where the prompt command is.
-
SQL without
"Prompt for Value" field
SQL with
"Prompt for Value" field
SELECT * FROM STUDENT
WHERE STD_HOMEROOM = ’101’
SELECT * FROM STUDENT
WHERE STD_HOMEROOM = ’{prompt:
-
SQL Syntax for…
Function
Prompt
- Constant, must be "prompt"
Field ID
- Data Dictionary ID for a field
- Determines the type of value to be retrieved and the label to appear before the prompt
- Field
ID can also be one of the following constants and will
result in an appropriate input for the type:
Text: Input field for text
Character: Input field for text
Integer: Input field for an integer
Number: Input field for a number. Decimal places are allowed.
Date: Input field for a date, with date picker
Time: Input field for a time value
Logical: Checkbox field. This field will produce a zero (0) or one (1) in the generated SQL, which is appropriate for all logical database fields. It might not correspond to other Boolean operations in SQL.Default type (optional)
- Type of default value that should appear in the prompt screen.
- Allows users to keep this value or replace it with their own.
- Can be blank if there is no default; or can be either const or default.
- See the following table on Default type = default.
Default value (optional)
- Default value to appear in the field’s prompt.
- If default type is const, this is a suitable input value for comparison.
Note: Default type and Default value can be left blank if they are not needed, but their positions should be marked with a colon ( : ) if the label is used and they are not.
Label (optional)
- Label displays when user is prompted for input value.
- If this is not present, the label will come from the user label on the field Id.
When Default type = default, a relevant value is looked up and placed as the Default value:
Default Value |
Function |
---|---|
today |
current date |
now |
current date (same as "today") |
districtYear |
last day of |
districtYearEnd |
last day of |
districtYearStart |
first day of |
firstOfMonth |
first day of current month |
firstOfWeek |
first day of current week |
lastOfMonth |
last day of current month |
lastOfWeek |
last day of the current week |
Note: The following are only useful in the School view and retrieve values for the current school: |
|
schoolName |
current school name |
schoolOld |
previous school name |
schoolStartGrade |
school start grade |
schoolEndGrade |
school end grade |
Examples of prompt commands in SQL:
Prompt |
Function |
---|---|
{prompt:stdHomeroom} |
|
{prompt:psnLastName: |
|
{prompt:psnDob:default:today} |
|
{prompt:psnEmail01:::Main Email Address}
|
|
{prompt:Text:const:Mary:Student first name}
|
|
{prompt:Date:default:today}
|
|