---
title: "SQL Data Source"
canonical: "https://support.appfire.com/space/PSJ/1589019609/SQL%20Data%20Source"
format: markdown
---
> Macro (aura-html)

> Macro (button-handy)




| **Script type** | **Description** | **Required for** |
| --- | --- | --- |
| SQL Initial Script | Defines database queries to retrieve field values. | - Fields without autocomplete.
- Autocomplete fields when minimum characters is set to 0. |
| SQL Autocomplete Script | Controls how search results appear as users type. | - Fields with autocomplete. |

![This screenshot shows the SQL Data Source option selected in the Data Source section of the custom field configuration page.](media://0de3c5b4-606d-4067-8b3c-2640d233fc80)

---

## SQL query results format

SQL queries can return data in two formats.

| **Select Format** | **How It Works** | **Example Query** | **Result Structure** |
| --- | --- | --- | --- |
| Single column | The selected value is used for both display and storage | `SELECT 'Project A' FROM projects` | Value "Project A" appears and is stored as "Project A". |
| Two columns | The first column is shown to users; the second column is stored in the system. | `SELECT 'Project A', 'PRJ-01' FROM projects` | Shows "Project A" to users; stores "PRJ-01" in the system. |

---

## SQL configuration examples

### Example 1: Autocomplete Field (Minimum Characters = 0)

Requires both initial and autocomplete scripts to configure. 

#### SQL Initial Script

```sql
select manufacturer from cars
where status = 'available';
```

This script provides an initial dropdown list of manufacturers that have available cars.

#### SQL Autocomplete Script

```sql
select model from cars
where manufacturer like {query} || '%'
and status = 'available';
```

This script returns car models whose manufacturer name starts with the user's typed text.

> ✅ For autocomplete scripts using the `like` operator, use the concatenation symbol (`||`) to combine the user's input (`{query}`) with the `%` symbol.When using the `like` operator in SQL scripts, use the concatenation symbol (`||`) to combine the query with the `%` symbol.

### Example 2: Autocomplete Field (minimum characters > 0)

Requires both initial and autocomplete scripts to configure.

#### SQL Autocomplete Script

```sql
select brand from car_parts
where name like {query} || '%';
```

This script filters car part names based on what the user types, showing matches that start with the typed text.

### Example 3: Field without autocomplete

Requires only initial script.

#### SQL Initial Script

```sql
select id from car_parts
where brand like 'Toyota';
```

This script retrieves the IDs of all Toyota car parts.

---

## Using SQL Datasource with different databases

The SQL syntax for filtering results varies depending on your database type. Here's how to structure your queries for different databases. 

### Standard SQL syntax

Most databases use the concatenation operator (`||`) to combine the search query with wildcards.

```sql
select name from TestTable
where name like {query} || '%'
```

### Oracle database syntax

Oracle databases require special handling of the LIKE operator and wildcards, with syntax varying by JDBC driver version.

- Use these patterns when working with ojdbc6.jar:

| Search Type | SQL Syntax | Description |
| --- | --- | --- |
| Start of text | `where name like {query%}` | Finds values that begin with the search text. |
| End of text | `where name like {%query}` | Finds values that end with the search text. |
| Any position | `where name like {%query%}` | Finds values containing the search text. |

- Due to a `LIKE` operator parsing issue in ojdbc7.jar, use the `CONCAT` operator instead:

| Search Type | SQL Syntax | Description |
| --- | --- | --- |
| Start of text | `where name like concat({query}, '%')` | Finds values that begin with the search text. |
| End of text | `where name like concat('%', {query})` | Finds values that end with the search text. |
| Any position | `where name like concat(concat('%', {query}), '%')` | Finds values containing the search text. |