# The SQL Parameter

### How It Works

1. CSV Getter fetches your full dataset from Airtable/Notion/CSV.
2. The data is loaded into an in-memory SQLite database as a table called **`csvgetter`**.
3. Your SQL query runs against that table.
4. The filtered/transformed result is returned.

> **Important:** The table name is always `csvgetter`. Use this in your `FROM` clause.

***

### Basic Syntax

```
?sql=SELECT * FROM csvgetter
```

URL-encoded:

```
?sql=SELECT%20*%20FROM%20csvgetter
```

> **Tip:** The URL Wizard in the dashboard builds and encodes URLs for you automatically.

***

### Examples

#### 1. Select All Data

```sql
SELECT * FROM csvgetter
```

```
?sql=SELECT%20*%20FROM%20csvgetter
```

#### 2. Select Specific Columns

```sql
SELECT name, email, status FROM csvgetter
```

```
?sql=SELECT%20name%2C%20email%2C%20status%20FROM%20csvgetter
```

#### 3. Filter with WHERE

```sql
SELECT * FROM csvgetter WHERE status = 'Active'
```

```
?sql=SELECT%20*%20FROM%20csvgetter%20WHERE%20status%20%3D%20%27Active%27
```

#### 4. Multiple Conditions (AND / OR)

```sql
SELECT * FROM csvgetter WHERE status = 'Active' AND department = 'Engineering'
```

```sql
SELECT * FROM csvgetter WHERE status = 'Active' OR status = 'Pending'
```

#### 5. Comparison Operators

```sql
SELECT * FROM csvgetter WHERE age > 25
```

```sql
SELECT * FROM csvgetter WHERE salary >= 50000 AND salary <= 100000
```

```sql
SELECT * FROM csvgetter WHERE hire_date > '2024-01-01'
```

#### 6. LIKE (Pattern Matching)

```sql
SELECT * FROM csvgetter WHERE name LIKE 'John%'
```

```sql
SELECT * FROM csvgetter WHERE email LIKE '%@gmail.com'
```

```sql
SELECT * FROM csvgetter WHERE city LIKE '%York%'
```

#### 7. IN (Multiple Values)

```sql
SELECT * FROM csvgetter WHERE department IN ('Engineering', 'Marketing', 'Sales')
```

#### 8. ORDER BY (Sorting)

```sql
SELECT * FROM csvgetter ORDER BY name ASC
```

```sql
SELECT * FROM csvgetter ORDER BY created_date DESC
```

```sql
SELECT * FROM csvgetter ORDER BY department ASC, salary DESC
```

#### 9. LIMIT (Row Count)

```sql
SELECT * FROM csvgetter LIMIT 10
```

```sql
SELECT * FROM csvgetter ORDER BY created_date DESC LIMIT 5
```

#### 10. LIMIT with OFFSET (Pagination)

```sql
SELECT * FROM csvgetter LIMIT 10 OFFSET 20
```

This skips the first 20 rows and returns the next 10.

#### 11. COUNT (Aggregation)

```sql
SELECT COUNT(*) as total FROM csvgetter
```

```sql
SELECT status, COUNT(*) as count FROM csvgetter GROUP BY status
```

**Sample output (as JSON):**

```json
[
  {"status": "Active", "count": 42},
  {"status": "Pending", "count": 15},
  {"status": "Closed", "count": 8}
]
```

#### 12. SUM, AVG, MIN, MAX

```sql
SELECT SUM(amount) as total_amount FROM csvgetter
```

```sql
SELECT department, AVG(salary) as avg_salary FROM csvgetter GROUP BY department
```

```sql
SELECT MIN(price) as cheapest, MAX(price) as most_expensive FROM csvgetter
```

#### 13. GROUP BY

```sql
SELECT department, COUNT(*) as headcount, AVG(salary) as avg_salary
FROM csvgetter
GROUP BY department
```

#### 14. GROUP BY with HAVING

```sql
SELECT department, COUNT(*) as headcount
FROM csvgetter
GROUP BY department
HAVING COUNT(*) > 5
```

#### 15. DISTINCT

```sql
SELECT DISTINCT department FROM csvgetter
```

```sql
SELECT DISTINCT city, state FROM csvgetter ORDER BY city
```

#### 16. NULL Handling

```sql
SELECT * FROM csvgetter WHERE phone IS NOT NULL
```

```sql
SELECT * FROM csvgetter WHERE notes IS NULL
```

#### 17. String Functions

```sql
SELECT UPPER(name) as name_upper, LOWER(email) as email_lower FROM csvgetter
```

```sql
SELECT * FROM csvgetter WHERE LENGTH(description) > 100
```

#### 18. CASE Expressions

```sql
SELECT name,
  CASE
    WHEN salary > 100000 THEN 'Senior'
    WHEN salary > 50000 THEN 'Mid'
    ELSE 'Junior'
  END as level
FROM csvgetter
```

#### 19. Column Aliases

```sql
SELECT name AS full_name, email AS contact_email FROM csvgetter
```

***

### Column Names with Spaces

If your column names contain spaces, wrap them in double quotes:

```sql
SELECT "First Name", "Last Name", "Email Address" FROM csvgetter
```

```sql
SELECT * FROM csvgetter WHERE "Job Title" LIKE '%Manager%'
```

***

### Combining SQL with Other Parameters

The `sql` parameter works alongside other URL parameters:

```
?sql=SELECT name, email FROM csvgetter WHERE status='Active'&type=json_records&email_me=true
```

Processing order:

1. Data is fetched from the source
2. `sql` query is applied
3. `type` formatting is applied
4. Side effects (`email_me`, `save_to_gdrive`, etc.) execute

***

### Error Handling

If your SQL is invalid, you'll get a **400** response:

```json
{
  "error": "Could not validate sql.",
  "data": "no such column: nonexistent_column",
  "help": "https://docs.csvgetter.com/sql-parameter"
}
```

Common errors:

| Error                    | Cause                          | Fix                                           |
| ------------------------ | ------------------------------ | --------------------------------------------- |
| `no such column: X`      | Column name doesn't exist      | Check your field names in the endpoint config |
| `no such table: X`       | Wrong table name               | Always use `FROM csvgetter`                   |
| `near "X": syntax error` | SQL syntax error               | Check your SQL syntax                         |
| `unrecognized token`     | Special characters not handled | Use URL encoding                              |

***

### Supported SQL Features (SQLite)

Since the SQL engine uses SQLite, you have access to:

* `SELECT`, `FROM`, `WHERE`, `ORDER BY`, `GROUP BY`, `HAVING`, `LIMIT`, `OFFSET`
* Aggregate functions: `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`
* String functions: `UPPER`, `LOWER`, `LENGTH`, `SUBSTR`, `TRIM`, `REPLACE`
* `LIKE`, `IN`, `BETWEEN`, `IS NULL`, `IS NOT NULL`
* `CASE ... WHEN ... THEN ... ELSE ... END`
* `DISTINCT`
* `AND`, `OR`, `NOT`
* Comparison: `=`, `!=`, `<`, `>`, `<=`, `>=`
* Math: `+`, `-`, `*`, `/`, `%`, `ABS`, `ROUND`

**Not supported:**

* `JOIN` (there's only one table)
* `INSERT`, `UPDATE`, `DELETE` (read-only)
* `CREATE TABLE`, `ALTER TABLE`, `DROP` (read-only)
* Subqueries (limited support — depends on complexity)