---
title: "My expectation does not exist"
description: "There is no 'count between 10 and 20' expectation — and there does not need to be: put the condition into the query and expect set is empty. The generic principle, with examples and CAT Pilot"
url: "https://docs.justcat.it/how-to-guides/test-patterns/my-expectation-does-not-exist/"
---
# My expectation does not exist


## The move

Keep the query that computes the number. Add the condition that means *something is wrong* — as a `HAVING` or a `WHERE` on a derived table — and expect `set is empty`. Two things happen: the test passes exactly when the rule holds, and when it fails the failure message shows the **actual value**, because the row that came back carries it.

"I expect `COUNT(*)` to be between 10 and 20" — as you fill it in on the Tests page of CAT Studio, and as YAML:


**Properties**



Name
: Between 10 and 20 active campaigns

Data source
: CRM

Query
: ```sql
  SELECT  COUNT(*) AS ActiveCampaigns
  FROM    dbo.Campaign
  WHERE   Status = 'Active'
  HAVING  COUNT(*) NOT BETWEEN 10 AND 20
  ```

Expectation
: set is empty




**YAML**


```yaml
Tests:
- Name: Between 10 and 20 active campaigns
  Data source: CRM
  Query: |
    SELECT  COUNT(*) AS ActiveCampaigns
    FROM    dbo.Campaign
    WHERE   Status = 'Active'
    HAVING  COUNT(*) NOT BETWEEN 10 AND 20
  Expectation: set is empty
```




Passes: no row. Fails: one row, `ActiveCampaigns = 27`, right there in the message — not just "failed".

## More of the same move

**A number within a range of another number** — this month's revenue within 5 % of last month's:

```sql
SELECT  ThisMonth, LastMonth
FROM    (SELECT SUM(CASE WHEN Period = @this THEN Amount END) AS ThisMonth,
                SUM(CASE WHEN Period = @last THEN Amount END) AS LastMonth
         FROM fact.Revenue WHERE Period IN (@this, @last)) t
WHERE   ABS(ThisMonth - LastMonth) > 0.05 * LastMonth
```

**No gaps in a sequence** — invoice numbers:

```sql
SELECT  InvoiceNo + 1 AS MissingFrom
FROM    dbo.Invoice i
WHERE   NOT EXISTS (SELECT 1 FROM dbo.Invoice j WHERE j.InvoiceNo = i.InvoiceNo + 1)
        AND InvoiceNo < (SELECT MAX(InvoiceNo) FROM dbo.Invoice)
```

**A ratio under a threshold** — at most 1 % of orders without a shipping address:

```sql
SELECT  COUNT(*) AS Orders, SUM(CASE WHEN ShipAddressId IS NULL THEN 1 ELSE 0 END) AS NoAddress
FROM    fact.Orders
HAVING  SUM(CASE WHEN ShipAddressId IS NULL THEN 1 ELSE 0 END) > 0.01 * COUNT(*)
```

**A measure in a range** (DAX) — the model's total within what the business signed off:

```dax
EVALUATE FILTER(ROW("Total", [Total Sales]), NOT ([Total] >= 1200000 && [Total] <= 1300000))
```

Each of them: the query returns the offending numbers, the expectation is `set is empty`; the test looks exactly like the first one with the query swapped.

> **🐱:** **You do not have to find the query yourself.** Describe the rule in a sentence to [CAT Pilot](https://docs.justcat.it/reference/cat-studio/cat-pilot/ "CAT Pilot") in CAT Studio — or to any AI assistant — name the data source, and it answers with exactly this shape: keep the query, add the condition that means *wrong*, expect `set is empty`. They are good at this job; run the query once, read the result, then save the test.



## Why CAT does not have more expectations

An expectation that encoded "between 10 and 20" would be one of hundreds — between, not between, within percent, monotonic, unique, … — each a small language to learn, each weaker than SQL. The five that exist say what a **result** must look like; the rule stays in the query, where you can run it in any client, explain it to a colleague and extend it without reading a manual. `set row count` is the one exception, kept because "exactly N" is so common; even that one is `HAVING COUNT(*) <> N` in disguise.

And because the rule lives in the query, an assistant can write it for you — see the tip above.

## Related

* [Which expectation when](https://docs.justcat.it/how-to-guides/test-patterns/which-expectation-when/ "Which expectation when") · [Find problems with set is empty](https://docs.justcat.it/how-to-guides/test-patterns/find-problems-with-set-is-empty/ "Find problems with set is empty")
* [Set is empty](https://docs.justcat.it/reference/tests/expectations/set-is-empty/ "Set is empty") — the reference, including how much of the result is read.