Define the decision and input records
TRIM and NULLIF treat an email containing only spaces as missing. Amount uses IS NULL: zero is a present value and may be legitimate under the chosen business rule. Separate flags let the reviewer correct every missing field in one pass rather than seeing only a single failed label.
All records below are synthetic. Create these tables in a fresh SQLite database before running the read-only query. Quantities are units; monetary values are integer HKD cents, so divide by 100 when presenting HK dollars.
| id | external_id | amount_cents | business_date | |
|---|---|---|---|---|
| 1 | X1 | a@example.test | 10000 | 2026-10-01 |
| 2 | X2 | [1 space character] | 20000 | 03/04/2026 |
| 3 | X3 | NULL (unknown or absent) | NULL (unknown or absent) | NULL (unknown or absent) |
| 4 | X4 | d@example.test | 0 | 2026-02-30 |
CREATE TABLE import_rows(id INTEGER PRIMARY KEY, external_id TEXT, email TEXT, amount_cents INTEGER, business_date TEXT);
CREATE TABLE sync_attempts(id INTEGER PRIMARY KEY, external_id TEXT, outcome TEXT);
CREATE TABLE target_records(id TEXT PRIMARY KEY, external_id TEXT UNIQUE);
INSERT INTO import_rows VALUES (1,'X1','a@example.test',10000,'2026-10-01'),(2,'X2',' ',20000,'03/04/2026'),(3,'X3',NULL,NULL,NULL),(4,'X4','d@example.test',0,'2026-02-30');
INSERT INTO sync_attempts VALUES (1,'X1','success'),(2,'X2','success'),(3,'X3','failed');
INSERT INTO target_records VALUES ('T1','X1');Run the query and compare the result
SELECT id,
CASE WHEN NULLIF(TRIM(email),'') IS NULL THEN 1 ELSE 0 END AS missing_email,
CASE WHEN amount_cents IS NULL THEN 1 ELSE 0 END AS missing_amount
FROM import_rows WHERE NULLIF(TRIM(email),'') IS NULL OR amount_cents IS NULL
ORDER BY id;| id | missing_email | missing_amount |
|---|---|---|
| 2 | 1 | 0 |
| 3 | 1 | 1 |
Row 2 is missing email only. Row 3 is missing both email and amount. Row 4’s zero amount passes the presence check, though its invalid date fails another check. Passing required-field validation does not mean the entire row is safe to import.
Apply the result within its limits
This is a completeness check, not an email deliverability or accounting validation rule. Add separately agreed type, range and reference checks. Keep rejected rows in a correction file and preserve their external IDs for a repeat import.
The supplied setup and result were executed with SQLite. Adapt the schema and status rules to a test copy of your system, then agree the expected result with the process owner. These examples have not been tested on other database platforms.