Query Editor

Top  Previous  Next

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.

HideTables - Hides the tables section of the query editor

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

arrowup - Move selected table up in the Selected (joined) tables list

arrowdown - Move selected table down in the Selected (joined) tables list

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

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

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

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






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.

plus_h - Add where clause(The editor will add an AND between items)

delete - Delete selected where clause

edit - Edit where clause



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.

plus_h - Add an order by clause

delete - Delete selected order by clause

edit - Edit selected order by clause