Difference between revisions of "Query the Database"
(First draft up to Entering Criteria) |
(→Entering Criteria: Added complex example) |
||
| Line 16: | Line 16: | ||
The Filter is defined on a row-by-row basis & you can enter multiple conditions. | The Filter is defined on a row-by-row basis & you can enter multiple conditions. | ||
| − | * To | + | * To add your first condition use the '''press the button to add a new condition'''. |
| + | ** To remove a condition, press the '''...''' button and select ''Remove Row'' | ||
| + | |||
* Click on the field name (in green) to open a list of fields in the query. | * Click on the field name (in green) to open a list of fields in the query. | ||
** You can select with the mouse, or by starting to type the field name as it's shown. | ** You can select with the mouse, or by starting to type the field name as it's shown. | ||
** Fields are often prefixed with their section names, e.g. Patient.Given Name. | ** Fields are often prefixed with their section names, e.g. Patient.Given Name. | ||
| + | |||
* Click the operator (in red) to select the comparison type. | * Click the operator (in red) to select the comparison type. | ||
** In character fields, ''Like'' can be used instead of ''Equals'' to partially match the value you enter. | ** In character fields, ''Like'' can be used instead of ''Equals'' to partially match the value you enter. | ||
| Line 26: | Line 29: | ||
* Click on the value (in blue) to change it. The type of editor you're presented with depends on the type of the field you're comparing. E.g. a date field will show a date editor, a drop-down field will show the same drop-down options. | * Click on the value (in blue) to change it. The type of editor you're presented with depends on the type of the field you're comparing. E.g. a date field will show a date editor, a drop-down field will show the same drop-down options. | ||
| + | |||
| + | By default, the conditions you enter are combined using the logical operator '''and'''. This is shown at the top of the filter, at the ''<Root>'' level. This means that all conditions you specify must be met for a row of data to be returned. Set this to ''Or'' to have rows returned where a row matches ''any'' condition. | ||
[[File:Ibid query filter definition sample.png]] | [[File:Ibid query filter definition sample.png]] | ||
| + | |||
| + | |||
| + | === Filter Groups === | ||
| + | |||
| + | In some circumstances you may have a complex filter to define that requires all of certain conditions to be met and any of some other conditions. We would achieve this effect by using a ''condition group''. A group of conditions has their own operator (and/or) that is treated separately from the <root> conditions. | ||
| + | |||
| + | This is best demonstrated by example: | ||
| + | |||
| + | [[File:Ibid query filter definition sample complex.png]] | ||
| + | |||
| + | The above conditions can be summarised as follows: | ||
| + | |||
| + | ([Record Type] = 'Acute major') OR ( ([Intubated] = TRUE ) AND ( ([Inhale Severity] = 'Severe') OR ([Inhale Severity] = 'Moderate') ) ) | ||
| + | |||
| + | aka | ||
| + | |||
| + | Record type is set to acute major | ||
| + | OR | ||
| + | Intubated is true AND Inhale Severity is Severe or Moderate | ||
| + | |||
| + | |||
| + | === Saving & loading filters for future use === | ||
==Selecting Fields to Output== | ==Selecting Fields to Output== | ||
Revision as of 18:01, 15 May 2013
This topic is for AquilaCRS
Contents
Selecting the Query
The sidebar on the Dashboard page contains a list of queries & searches. To initiate a query just press on the option you require. The options differ only in the data tables they search. For instance, the IBID Query allows you to query the IBID set of data tables.
When you select the Query of your choice, you may need to wait for a few seconds initially whilst all the fields and their parameters are transferred from the server.
Queries have two elements:
- The Filter : this is a set of conditions that determine which rows are returned from the database. E.g. Sex = Male
- Output Fields: this is a set of tickboxes that determine which fields are listed in the query output.
Entering Criteria
The Filter is defined on a row-by-row basis & you can enter multiple conditions.
- To add your first condition use the press the button to add a new condition.
- To remove a condition, press the ... button and select Remove Row
- Click on the field name (in green) to open a list of fields in the query.
- You can select with the mouse, or by starting to type the field name as it's shown.
- Fields are often prefixed with their section names, e.g. Patient.Given Name.
- Click the operator (in red) to select the comparison type.
- In character fields, Like can be used instead of Equals to partially match the value you enter.
- E.g. Patient.Town like chester will match manchester and chester, but Patient.Town equals chester will only match Chester.
- In name fields, Like will perform a sounds like match.
- In character fields, Like can be used instead of Equals to partially match the value you enter.
- Click on the value (in blue) to change it. The type of editor you're presented with depends on the type of the field you're comparing. E.g. a date field will show a date editor, a drop-down field will show the same drop-down options.
By default, the conditions you enter are combined using the logical operator and. This is shown at the top of the filter, at the <Root> level. This means that all conditions you specify must be met for a row of data to be returned. Set this to Or to have rows returned where a row matches any condition.
Filter Groups
In some circumstances you may have a complex filter to define that requires all of certain conditions to be met and any of some other conditions. We would achieve this effect by using a condition group. A group of conditions has their own operator (and/or) that is treated separately from the <root> conditions.
This is best demonstrated by example:
The above conditions can be summarised as follows:
([Record Type] = 'Acute major') OR ( ([Intubated] = TRUE ) AND ( ([Inhale Severity] = 'Severe') OR ([Inhale Severity] = 'Moderate') ) )
aka
Record type is set to acute major OR Intubated is true AND Inhale Severity is Severe or Moderate
Saving & loading filters for future use
Selecting Fields to Output
{ todo }
Query Results
See Query results for more information
