# Using SQL CASE

```json metadata
{
  "schema_version": 2,
  "title": "Using SQL CASE",
  "description": "",
  "categories": ["docs","integrate","rs","rdi"],
  "group": "di",
  "tableOfContents": {"sections":[{"id":"using-sql-case-to-create-a-new-field","title":"Using SQL CASE to create a new field"},{"id":"using-sql-case-to-filter-data","title":"Using SQL CASE to filter data"}]}

,
  "codeExamples": []
}
```


The [`CASE`](https://www.w3schools.com/sql/sql_case.asp) statement allows you to specify conditions and return different values based on those conditions. You can use it both to create new fields or filter existing data.

## Using SQL CASE to create a new field
The example below demonstrates how to use the `CASE` statement to create a new field called `Market` based on the value of the `BillingCountry` field in the `Invoice` table. The new field categorizes countries into regions such as "North America" and "Europe".

```yaml
name: Add market field using SQL CASE
source:
  table: Invoice

transform:
  - uses: add_field
    with:
      field: "Market"
      language: sql
      expression: |
        CASE
          WHEN BillingCountry = 'USA' THEN 'North America'
          WHEN BillingCountry = 'Canada' THEN 'North America'
          WHEN BillingCountry = 'UK' THEN 'Europe'
          WHEN BillingCountry = 'France' THEN 'Europe'
          ELSE 'Other'
        END
```

## Using SQL CASE to filter data

You can also use the `CASE` statement to filter data based on specific conditions. The example below demonstrates how to filter the `Invoice` table to include only invoices from the USA and Canada that have a `Total` value above their country-specific threshold.

Because the `Total` field is a Decimal in the source table, it is represented as a string in Debezium and so you must cast it to `REAL` to compare it numerically in the `CASE` statement. Without this cast, it will be compared as a string value, which will give the wrong result.

```yaml
name: Filter invoices by country and total
source:
  table: Invoice

transform:
  - uses: filter
    with:
      language: sql
      expression: |
        CASE
          WHEN BillingCountry = 'USA' AND CAST(Total AS REAL) > 11.99 THEN True
          WHEN BillingCountry = 'Canada' AND CAST(Total AS REAL) > 9.99 THEN True
          ELSE False
        END
```