• Cross-Folder Queries
• Cross-Project Queries
• Use ContainerFilter in SQL
• Use Module Properties
• Fields With Dependencies

Cross-Folder Queries

You can perform cross-folder queries by identifying the folder that contains the data of interest during specification of the dataset. The path of the dataset is composed of the following components, strung together with a period between each item:

• Project - This is literally the word Project, which is a virtual schema and resolves to the current folder's project.
• Path to the folder containing the dataset, surrounded by quotes. This path is relative to the current project. So a dataset located in the Home > Tutorials > Demo subfolder would be referenced using "Tutorials/Demo/".
• Schema name - In the example below, this is study
• Dataset name - Surrounded by quotes if there are spaces in the name. In the example below, this is "Physical Exam"

Example

The Edit SQL Query Source topic includes a simple query on the "Physical Exam" dataset which looks like this, when the dataset is in the local folder:

SELECT "Physical Exam".ParticipantID, ROUND(AVG("Physical Exam".Temp_C), 1) AS AverageTemp
FROM "Physical Exam"
GROUP BY "Physical Exam".ParticipantID

This query could reference the same dataset from a sibling folder within the same project. To do so, you would replace the string used to identify the dataset in the FROM clause ("Physical Exam" in the query used in this topic) with a fully-specified path. For this dataset, you would use:

Project."Tutorials/Demo/".study."Physical Exam"

Using the query from the example topic as a base, the cross-folder verson would read:

SELECT "Physical Exam".ParticipantID, ROUND(AVG("Physical Exam".Temp_C), 1) AS AverageTemp
FROM Project."Tutorials/Demo/".study."Physical Exam"
GROUP BY "Physical Exam".ParticipantID

Cross-Project Queries

You can perform cross-project queries using the full path for the project and folders that contain the dataset of interest. To indicate that a query is going across projects, use a full path, starting with a slash. The syntax is "/<FULL FOLDER PATH>".<SCHEMA>.<QUERY>

• Full path to the folder containing the dataset, surrounded by quotes. This lets you access an arbitrary folder, not just a folder in the current project. You can use a leading / slash in the quoted path to indicate starting at the site level. You can also use the virtual schema "Site." to lead your path, as shown below in the module property example. For example, a dataset located in the Tutorials > Demo subfolder would be referenced from another project using "/Tutorials/Demo/".
• Schema name - In the example below, this is study
• Dataset name - Surrounded by quotes if there are spaces in the name. In the example below, this is "Physical Exam"

The example shown above can be rewritten using cross-project syntax by including the entire path to the dataset of interest in the FROM clause, preceded by a slash.

"/Tutorials/Demo/".study."Physical Exam"

Using the query from the example topic as a base, the cross-folder verson would read:

SELECT "Physical Exam".ParticipantID, ROUND(AVG("Physical Exam".Temp_C), 1) AS AverageTemp
FROM "/Tutorials/Demo/".study."Physical Exam"
GROUP BY "Physical Exam".ParticipantID

Example 2

SELECT Demographics.ParticipantId,
Demographics.Language,
Languages.TranslatorName,
Languages."PhoneNumber",
FROM Demographics
JOIN "/Other/Folder".lists.Languages ON Demographics.Language=Languages.Language

Use ContainerFilter in SQL

You can annotate individual tables in the FROM clause with an optional container filter if you want to restrict or expand the scope of the individual table in a larger query. For example, this would allow an issues report to specify that the query should use CurrentAndSubfolder without having to create a corresponding custom view. Further, the defined container filter can not be overridden by a custom view.

The syntax for this is:

SELECT * FROM Issues [ContainerFilter='CurrentAndSubfolders']

Find the list of possible values for the containerFilter here. The value must be surrounded by single quotes and capitalized exactly as expected. In SQL this is with a leading capital letter as shown in the lower section of that linked list of values.

This option can be used in combination with other SQL syntax, including the cross-folder and cross-project options above.

Use Module Properties

You can specify the path for a cross-folder or cross-project query using a module property, allowing you to define a query to apply to a different path or dataset target in each container. For example, if you define the module property "DatasetPathModuleProp" in the module "MyCustomModule", the syntax to use would be:

SELECT
source.ParticipantId
FROM Site.{moduleProperty('MyCustomModule', 'DatasetPathModuleProp')}.study.source

In each container, you would define this module property to be the full path to the desired location.

Fields with Dependencies

A few LabKey fields/columns have dependencies. To use a field with dependencies in a custom SQL query, you must explicitly include supporting fields.

To use Assay ID in a query, you must include the run's RowId and Protocol columns. You must also use these exact names for the dependent fields. RowId and Protocol provide the Assay ID column with data for building its URL.

If you do not include the RowId and Protocol columns, you will see an error for the Run Assay ID field. The error looks something like this:

"KF-07-15: Error: no protocol or run found in result set."

Related Topics

• Controlling Data Scope
• LabKey SQL Reference
• Using SQL in Java Modules
• LabKey SQL Utility Functions