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

AdminQueryEditorWithNormalize

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.

QueryEditorTableSelector

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

QueryEditorTableJoins

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)

QueryEditorAddJoinCondition

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)

 

QueryEditorFieldSelector

 

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

 

 

QueryEditorWhereClauseAndOrderBy

 

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.

plus_h - Add where clause(The editor will add an AND between items)
delete - Delete selected where clause
edit - 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.

plus_h - Add an order by clause
delete - Delete selected order by clause
edit - Edit selected order by clause