A querie is a logical question that you ask the database, meaning, a search in the database.
In the Queries tab, there are a number of object and subtypes grouped in folders showing basically all object types on which queries can be created. In the User queries folder, the application saves the questions you have created. The Standard queries folder contains frequently asked questions. These are joined by a number of folders with object types grouped by business function and/or which part of the network they belong to.
Run a query
1.Select Tools > Query tool.
2.Select the Queries tab.
3.Select folder in the folder structure.
4.Select an object type/subtype or a predefined query. Double-click to run the query without specifying conditions.
5.Enter any search criteria and/or geographical delimitation.
6.Press Search. The selection is displayed in the results window.
Search criteria
In saved object list |
Run queries on results in saved object list. See use case Run queries on results in saved object list for example. |
Run queries on only visible states. |
|
Run queries on changesets only. |
Show results as file
When you run your own queries and in cases where you can expect a very large amount of results, you can advantageously choose to write the entire result directly to a file, without the entire amount being written in the results window.
1.Prepare your question according to section Run a query.
2.In Result as, check the File checkbox.
3.Press Search. The Save window opens.
4.Enter file name and file format. Example: "CustDistrB.doc".
5.Enter field separator (without quotes). Example: ";" "tabular".
6.Press Save.
User queries
You can create your own queries that are adapted to the information you often search for in the database. The queries you create are automatically saved in the folder User queries. These queries are linked to your personal user profile, only you have access to your own queries.
Create user query
1.Under the Queries tab, right-click on the object type you would like to base your query on and select Create query. The Create query for [object type] dialog opens.
2.Give the query a name in the Name of the query text field.
3.Select the attributes to be included in the query result by clicking the arrow buttons 
and 
to move rows between the Available list on the left side and the Selected list to the right. The rows in Selected correspond to the table columns that will be shown in the result window when you run the query.
|
The display field shows, within parentheses, which component the attribute belongs to. First, the attributes from the main component are displayed, followed by attributes from the other components in alphabetical order. Attributes with (0,*) indicate that the attribute can have multiple occurrences, thus resulting in more rows in the search result |
|
To re-order the columns use the |
4.In section Search on, prepare the search criteria that you complete when you run the query. First, select the attribute field in the drop-down list to the left and then select a suitable comparison operator to the right. See section Comparison operators for more information.
|
You can set whether the query is case sensitive or not. Press on |
or 
to set your preferences. Search fields that ignore case are marked with an asterisk (*) when you run the query.5.In section Sort by, use the drop-down list to fine-tune the presentation of query results.
6.Press OK to save your query.
|
•It is also possible to select attributes from non mandatory data components when you create your own queries. •If you add a Shape attribute when creating your own query, you can choose which Shape attributes to display directly in Selected. 1.Right-click the Shape attribute. A dialog box appears. 2.Select attributes and press OK to confirm. •It is possible to select coordinates and reference systems from symbol components. The symbol component must not have more than 1 occurrence in the metadata. Right-click on the selected Shape to choose whether x, y or z should be displayed in the result and whether the reference system should be displayed. |
Add more attributes from the codelist
1.To be able to select other columns from the code list than just the TBL linked fields, move the attribute from the Available list to the Selected list.
2.Right-click on the row the Selected list. A dialog where you can select more fields from the code list opens.
3.Select one or more rows in the Available list and press to add them to the Selected list.
4.Press OK.
Formulate an expression
When you create your own query, you can choose to prepare one or more expressions that you complete when you run the query. An expression specifies the conditions for the selection (results) that are retrieved from the database. An expression consists of:
•Field values - the object's attributes.
•Comparison operator - a comparison or geographical delineation.
•Constant - a specific data value.
Example: Suppose you would like to send a query to the database for routes longer than 250 meters. You can form your criteria in this way:
Criterion 1: Graphical length > 250
•Field value - Graphical length
•Comparison operator - >
•Constant - 250
Comparison operators
Comparison operator |
Description |
|---|---|
= |
Equal |
> |
Greater than |
> = |
Greater than or equal |
< |
Less than |
< = |
Less than or equal |
< > |
Not equal |
LIKE |
About (use together with the wildcards % and _) |
IS NULL |
Is empty |
IS NOT NULL |
Is not empty |
IN |
Several values from codelist |
Mer om LIKE-operatorn
LIKE används tillsammans med jokertecknen % och _ och är väldigt användbar om du bara känner till delar av namnet till det objekt som du söker.
More about the LIKE operator
LIKE is used in conjunction with wildcard characters % (percent) and _ (underscore) and is very useful when you want to search for a specific string value in the database.
Wildcard character |
Description |
Exemple |
|---|---|---|
% |
Is used to match any character or characters (letters or numbers) in a string. |
The search criterion 2% returns all string values beginning with the number 2. |
_ |
Is used to match only an optional character |
The search criterion _T__ returns all values containing a total of 4 characters that begin with any one character, followed by one T, and any two characters. |
|
|
Assuming that you set the following criterion for a station ID: % 5_, the query returns all strings where the number 5 is the next to last character, for example: 159 58 87555 |
More about the IN operator
IN is used to be able to select several values from a code list in your own query. IN can only be used for code list fields.
1.In the dialog Create query for [object type] section Search on, select a code list search term.
2.Select operator IN.
3.Press OK. The search term is now visible in the query tool for the created query.
4.Scroll to the bottom of the drop-down list for the search criteria and select Select several....
Edit own query
1.Right-click on the query and select Edit query [name of query].
2.Edit or add fields, expressions and sorting as needed.
3.Press OK. Your edits are saved.
|
To remove a query, right-click on the query and select Delete query [name of query]. |