Ad-hoc Query

The Ad-hoc query tool enables end users to submit form based queries created by IT specialists. Results can be viewed on screen, exported to files, or transferred to a user-created batchset for further processing.

Adding a new ad-hoc query

Adding a new ad-hoc query involves two simple steps.

1. You must determine which fields will be required for listing and for search. Once this is done, you can create a new structure that will refer to those fields. See structure for help on that topic.
2. Then, you need to add a new entry in datamart_adhoc. This new entry must refer to the structure created at the previous step. It must also define, in sql_query_for_results the sql query that will fetch the result.

sql_query_for_results guide lines

There are simple rules that must to be followed in order to make the query work.

• You do not need to end the queries with ";"
• You must have a white space between your field, your operator and your value.

Eg.: Participant.first_name = "@@Participant.first_name@@"

Counter eg.: Participant.first_name="@@Participant.first_name@@"

• If in you are going to use fields already declared in structure_fields and referring to models, you need to specify the name of the model for your tables. Here, we specify the model referring to aliquot_masters.

Eg.: SELECT * FROM aliquot_masters AS AliquotMaster

• You must use the double quotes (") for your WHERE parameters.
• Keywords, such as AND and OR must be written in uppercase.
• If you need a WHERE parameter to be replaced, and you most probably will, you must enclose the [Model].[Field] construct you are using (as declared in structure_fields) within double @.

Eg.: Let's say we are working on the Participant model and on the first_name field, we could have something like

SELECT * FROM participants AS Participant WHERE Participant.sex = "@@Participant.sex@@"

• If one of your search parameter is a date, it will automatically be shown as a range, and the return keys will have a suffix indicating wheter they are the start or the end of the range.

Eg.: You have the field Participant.date_of_birth. What you might want to do for your query is

[...] WHERE Participant.date_of_birth >= "@@Particpant.date_of_birth_start@@" AND Participant.date_of_birth <= "@@Particpant.date_of_birth_end@@"

• The use of IN and LIKE is forbidden with search parameters. You must use "=" instead, which will be replaced based on the "exact search" parameter. You can sill use IN and LIKE for non search parameters.

Eg.: [...] WHERE Participant.first_name LIKE "@@Participant.first_name@@" is forbidden

• The use of BETWEEN is also forbidden. You must use the field >= start AND x <= end form.

What happens to empty parameters

Once every received search parameters have been replaced, the query goes through a series of regular expression replace functions to remove empty parameters.