The query editor allows the user to create queries for Crash Magic. Clients using this editor must have a strong background in SQL and knowledge of the database in use. Studies queries are designed to create a flat collision record. This means that each row returned by the query will only contain one collision record. A query that returns a collision record in more than one row will cause users to receive duplicate record errors in Crash Magic.
The editor is divided up into four sections:
•Table selector
•Table joins
•Query fields
•Where and order by clauses
The query editor is used for the creation of all Crash Magic queries
The Table selector is used to select the tables that will be used in a query and specify the current join condition displayed in the Table joins section of the window.
In this example the Streets and Trafficunit tables have been selected several times
•Available tables - Tables available for use in the query
•Selected (joined) tables - Tables currently selected in the query. These tables will be displayed in the from clause of the query.
•
- Hides the tables section of the query editor
•
- Adds the highlighted table from the Available tables list to the Selected (joined) tables list( A table can be selected more than once to create self joins)
•
- Move selected table up in the Selected (joined) tables list
•
- Move selected table down in the Selected (joined) tables list
•
- Remove selected table from the Selected (joined) tables list
The Table Join section is used to tell how the selected table will be joined to the previous tables
In this example the table alias is Unit1.
Example Join created
LEFT JOIN dbo.TRAFFICUNIT Unit1 ON ((Unit1.Caseid=Crash.CaseID) AND (Unit1.UnitNumber=1)))
•Table drop down - Allows the user to move between the Selected joined tables
•
- Hides the table join in
•Table - Shows the name of the table selected
•Alias - Shows the alias name that will be used by the table in the query
•Join type - shows how the table will be joined in the from clause of the query(INNER, LEFT OUTER, RIGHT OUTER)
•Join conditions window - Shows the current join conditions for the table
• - Opens the Add linkage condition box(The add linkage creates the join on clause of an SQL query)
The drop down menu allows the user to select a field from the current table. The joined Table.Field can be entered in the box after the = sign.
• - Delete the selected join condition
•Current selected field
•Current joined field
•Auto-suffix - Suffix added to any field alias in the currently selected table(Recommended alias are _ONE, _TWO, _THREE for the first three vehicles)
•Available fields - The list of fields from the current selected table
• - Adds the selected field in the Available fields list to the Selected fields list
•Selected fields - These are the fields that will be returned by the query
•Field name - The name of the currently selected field in the Selected fields list
•Field alias - The field alias that will be used in the query(Any value from the Auto-Suffix box will be added to the field alias)
•Lookup value - This value must match the DBField name of the lookup query for the returned value to be looked up(Lookup values of !date, !time and !datetime can be specified for date, time and datetime fields respectively. These lookup values will cause the field to be formatted according to the locale information selected.)
•Normalized field - This drop down menu is used to flag the selected field as one of the normailzed fields used by Crash Magic
WHERE:
The where clause section is used to add items to the where clause of the query. Where clause items should be avoided in study queries as the Crash Magic will dynamically where clause information.
• - Add where clause(The editor will add an AND between items)
• - Delete selected where clause
•
- Edit where clause
ORDER BY:
The order by section will add an order by clause to the query. Study queries must have an order by clause to ensure records will be returned in the same order. Users may experience difficulties in using collisions diagrams if the study order by clause is missing.
• - Add an order by clause
• - Delete selected order by clause
• - Edit selected order by clause