SQL

yellowbox

This content will introduce you to basic concepts and terms related to Structured Query Language (SQL) as it relates to filters and Crystal Reports used by TruckMate.

SQL Statement Examples

To extract the contents of the STATUS table using the SQL Execute program, enter the statement:

SELECT * FROM STATUS

To extract only the STATUS_CODE field from the STATUS table, enter the statement:

SELECT STATUS_CODE FROM STATUS

To extract only records where the value in the STATUS_CODE field is 'AVAIL', you would enter the statement:

SELECT * FROM STATUS WHERE STATUS_CODE = 'AVAIL'

SQL Filters

In several TruckMate applications, users have the option of creating custom SQL Filters to sort records in any way they desire. The following TruckMate applications have access to SQL Filtering:

Billing Consolidation program
City Dispatch program
Customer Service program
Dispatch program
Driver Call-In program
Driver Pay Audit program
Equipment Rentals program
Invoice Printing program
Local Dispatch program
Multi Mode Dispatch program
Order Pre-Processing program
Pre-Paid Accounts program
Supplier Contracts program
System Integrity Checker program

Custom SQL Filter window

Every TruckMate application will share the SQL Filter window, as seen above, but the Fixed SQL (SELECT…FROM) SQL query will differ depending on the program (and/or program area) that is accessing the SQL Filtering functionality. In the above example, the SQL Filter window was accessed from the Customer Service program, so the Fixed SQL query is a SELECT… FROM the TLORDER table. When creating a filter for a TruckMate application, ensure that you look over the 'Fixed SQL…' query so that you understand what data you have access to when creating the User SQL (WHERE) clause.

If the fixed SELECT includes multiple tables, then the edit-pick accessed via tools (top menu or right-click/double-click on User SQL) will now show columns from all tables involved (not just the single main table). Note that if you select a column name that appears in more than one table, you must manually type in the table prefix (or you will get a SQL syntax error when you try to save).

Click the '+' button at the top of the SQL Filter window to begin creating a new SQL Filter.

This window has been enhanced so that any custom filter that takes longer than 1.0 seconds to run will not be saved to the database. This is to prevent users from entering custom SQL filters that would overly tax the database in a live environment.
When using an SQL Filter, the User can enter 'ORDER BY…' statements so that the data is displayed in a certain order.
There are several settings on the Security Setup program > Security > Business Events > Filters Tab that apply to the SQL Filter window.

Fixed SQL

This section of the Filter Setup screen displays the fixed portion of the SQL query SELECT * FROM TLORDER T WHERE. This cannot be changed. Selection criteria entered in the User SQL portion of the screen are appended to the fixed SQL statement to form the complete query.

User SQL

This area is used to enter the criteria by which you want to select bills. The criteria are phrased using the database field labels.

Public

If this flag is checked, this filter will be available to all TruckMate users. If the box is not checked, only the user who created the filter will be able to use or edit the filter.

Title

Before saving the filter, enter a title in the field beside the User SQL text. This title is used to select the filter on the Customer Service program > Summary Tab.

Select

Will apply the currently displayed filter and display all records that meet the selection criteria on the main Filter screen.

Cancel

Will abandon changes and return you to the main Filter screen.

Test/Benchmark

Will confirm that your SQL syntax is correct and will run the query to establish how long the data collection will take on your database.

Context Menu

Menu option	Description
Insert Column Name	It contains a new feature whereby you can select a column name from the main table (in this case TLORDER) and have it added to the filter (in the current cursor position)- meaning that you don’t have to have the SQL Execute program open browsing the columns list while you are creating your filter.
Import	This option allows the user to select a SQL query from another Filter, KPI or Dawg and paste it into the currently selected filter in the 'User SQL (Where)' memo field. If you try to use this option while your 'User SQL (Where)' is NOT blank, a warning will be issued; click 'Yes' if you want to overwrite your existing filter. The user can select a SQL from among a number of available SQLs.

Menu option

Description

Insert Column Name

It contains a new feature whereby you can select a column name from the main table (in this case TLORDER) and have it added to the filter (in the current cursor position)- meaning that you don’t have to have the SQL Execute program open browsing the columns list while you are creating your filter.

Import

This option allows the user to select a SQL query from another Filter, KPI or Dawg and paste it into the currently selected filter in the 'User SQL (Where)' memo field. If you try to use this option while your 'User SQL (Where)' is NOT blank, a warning will be issued; click 'Yes' if you want to overwrite your existing filter. The user can select a SQL from among a number of available SQLs.