# Query
Queries can be used for data mining. On the **Projects > your\_project > Base > Query** page:
* New queries can be created and executed
* Already executed queries can be found in the query history
* Saved queries and query templates are listed under the saved queries tab.
## New Query
### Available tables
All available tables are listed on the **Run** tab.
{% hint style="info" %}
Metadata tables are created by syncing with the Base module. This synchronization is configured on the **Details** page within the project.
{% endhint %}
#### Input
Queries are executed using SQL (for example `Select * From table_name`). When there is a syntax issue with the query, the error will be displayed on the query screen when trying to run it. The query can be immediately executed or saved for future use.
#### Best practices and notes
{% hint style="danger" %}
**Do not use queries such as ALTER TABLE to modify your table structure as it will go out of sync with the table definition and will result in processing errors.**
{% endhint %}
* When you have duplicate column names in your query, put the columns explicitly in the select clause and use column aliases for columns with the same name.
* Case sensitive column names (such as the VARIANTS table) must be surrounded by double quotes. For example, `select * from MY_TABLE where "PROJECT_NAME" = 'MyProject'`.
* The syntax for ICA case-sensitive subfields is without quotes, for example `select * from MY_TABLE where ica:Tenant = 'MyTenant'` As these are case sensitive, the upper and lowercasing must be respected.
* If you want to query data from a table shared from another tenant (indicated in green), select the table (**Projects > your\_project > Base > Tables > your\_table**) to see the unique name. In the example below, the query will be `select * from demo_alpha_8298.public.TestFiles`\
* For more information on queries, please also see the snowflake documentation:
#### Querying data within columns.
Some tables contain columns with an array of values instead of a single value.
### Querying data within an array
{% hint style="info" %}
As of ICA version 2.27, there is a change in the use of capitals for ICA array fields. In previous versions, the data name within the array would start with a capital letter. As of 2.27, lowercase is used. For example `ICA:Data_reference` has become `ICA:data_reference`.
You can use the GET\_IGNORE\_CASE option to adapt existing queries when you have both data in the old syntax and new data in the lowercase syntax. The syntax is `GET_IGNORE_CASE(Table_Name.Column_Name,'Array_field')`
For example:
`select ICA:Data_reference as MY_DATA_REFERENCE from TestTable` becomes:
`select GET_IGNORE_CASE(TESTTABLE.ICA,'Data_reference') as MY_DATA_REFERENCE from TestTable`
You can also modify the data to have consistent capital usage by executing the query `update YOUR_TABLE_NAME set ica = object_delete(object_insert(ica, 'data_name', ica:Data_name), 'Data_name')` and repeating this process for all field names (Data\_name, Data\_reference, Execution\_reference, Pipeline\_name, Pipeline\_reference, Sample\_name, Sample\_reference, Tenant\_name and Tenant\_reference).
{% endhint %}
Suppose you have a table called YOUR\_TABLE\_NAME consisting of three fields. The first is a name, the second is a code and the third field is an array of data called ArrayField:
| Examples |
| --------------------------------------------------------------------------------------------------------------------------------------------------------- |
| You can use the name field and code field to do queries by running |
| `Select * from YOUR_TABLE_NAME where NameField = "Name A"`. |
| If you want to **show specific data** like the email and bundle name **from the array**, this becomes |
| `Select ArrayField:userEmail as User_Email, ArrayField:bundleName as Bundle_Name from YOUR_TABLE_NAME where NameField = "Name A"`. |
| If you want to **use data in the array** as your selection criteria, the expression becomes |
| `Select ArrayField:userEmail as User_Email, ArrayField:bundleName as Bundle_Name from YOUR_TABLE_NAME where ArrayField:boolean = true`. |
| If your **criteria is text in the array**, use the `'` to delimit the text. For example: |
| `Select ArrayField:userEmail as User_Email, ArrayField:bundleName as Bundle_Name from YOUR_TABLE_NAME where ArrayField:userEmail = 'email_A@server.com'`. |
| You can also use the **LIKE** operator with the **% wildcard** if you do not know the exact content. |
| `Select ArrayField:userEmail as User_Email, ArrayField:bundleName as Bundle_Name from YOUR_TABLE_NAME where ArrayField:userEmail LIKE '%A@server%'` |
#### Query results
If the query is valid for execution, the result will be shown as a table underneath the input box. Only the first 200 chars of a string, record or variant field are included in the query results grid. The complete value is available through clicking the "show details" link.
From within the result page of the query, it is possible to save the result in several ways:
* **Export to > New table** saves the query result as a new table with contents.
* **Export to > New view** saves the query results as a new [view](https://help.ica.illumina.com/project/base-tables/datacatalogue#available-views).
* **Export to > Project file**: As a new table, as a view or as file to the project in CSV (Tab, Pipe or a custom delimeter is also allowed.) or JSON format. When exporting in JSON format, the result will be saved in a text file that contains a JSON object for each entry, similar to when exporting a [table](https://help.ica.illumina.com/tutorials/base_basics#export-table-data). The exported file can be located in the Data page under the folder named base\_export\_<*user\_supplied\_name*>\_<*auto generated unique id*>.
### Run a new query
1. Navigate to **Projects > your\_project > Base > Query**.
2. Enter the query to execute using SQL.
3. Select **Run**.
4. Optionally, select Save to add the query to your saved queries list.
If the **query takes more than 30 seconds** without returning a result, a message will be displayed to inform you the query is still in progress and the status can be consulted on **Projects > your\_project > Activity > Base Jobs**. Once this Query is successfully completed, the results can be found in **Projects > your\_project > Base > Query > Query History** tab.
## Query history
The query history lists all queries that were executed. Historical queries are shown with their date, executing user, returned rows and duration of the run.
1. Navigate to **Projects > your\_project > Base > Query**.
2. Select the **History** tab.
3. Select a query.
4. Perform one of the following actions:
* **Use**—Open the query for editing and running in the Run tab. You can then select **Run** to execute the query again.
* **Save** —Save the query to the saved queries list.
* **View Results**—Download the results from a query or export results to a new table, view, or file in the project. Results are available for 24 hours after the query is executed. To view results after 24 hours, you need to execute the query again.
## Saved Queries
All queries saved within the project are listed under the Saved tab together with the query templates.
The saved queries can be:
* **Use —** Open the query for editing and running in the Run tab. You can then select **Run** to execute the query again.
* **Saved as template —** The saved query becomes a query template.
* **Deleted —** The query is removed from the list and cannot be opened again.
The query templates can be:
* **Opened**: This will open the query again in the “New query” tab.
* **Deleted**: The query is removed from the list and cannot be opened again.
It is possible to edit the saved queries and templates by double-clicking on each query or template. Specifically for Query Templates, the data classification can be edited to be:
* **Account**: The query template will be available for everyone within the account
* **User**: The query template will be available for the user who created it
### Run a saved Query
If you have saved a query, you can run the query again by selecting it from the list of saved queries.
1. Navigate to **Projects > your\_project > Base > Query**.
2. Select the **Saved Queries** tab.
3. Select a query.
4. Select **Open Query** to open the query in the New Query tab from where it can be edited if needed and run by selecting **Run Query**.
## Shared database for project
Shared databases are displayed under the list of Tables as Shared Database for project \.
> For ICA Cohorts Customers, shared databases are available in a project Base instance. For more information on specific Cohorts shared database tables that are viewable, See [Cohorts Base](https://help.ica.illumina.com/project/p-cohorts/cohorts-base).
.png?alt=media)