This section highlights a few commonly used SQL queries that you can use within row filters and pass conditions in custom rules to achieve your desired outcomes.
| Use cases where SQL queries can be implemented in row filters | SQL Queries to be defined in the row filter to achieve the use case | Description | Use cases where SQL queries can be implemented in pass conditions | SQL Queries to be defined in the pass condition to achieve the use case | Description | Expected Outcome |
| Filter only active customers from the dataset . | SELECT * FROM Customers WHERE Status = 'Active' |
When applied as a row filter, this condition selects only those customers whose status is marked as Active. | Customers are required to have a valid email address and the value should not be null | SELECT * FROM Customers WHERE Email IS NOT NULL |
This condition is used to identify which customers have a non-null email address, counting them as passing the rule. | After the rule is evaluated, the row filters are evaluated accordingly and an ‘Evaluated’ count of assets is generated for active customers and a count of passed records is generated for those customers whose email address is not null. |
|
Users address must be Chicago city d. |
SELECT * FROM Customers WHERE City = 'Chicago' |
When this condition is set in the row filter, it selects only those customers whose city is listed as Chicago. | The addresses must be CASS certified | SELECT * FROM Customers WHERE CASS_CERTIFICATION = 'Y' |
This condition checks which customer records have CASS_CERTIFICATION marked as 'Y' | After evaluating the rule, an evaluated count is generated representing the customers belonging to Chicago city and a passed count is generated representing those users whose records are CASS Certified. |
| All customers are located in Japan (JPN) | SELECT * FROM Customers WHERE Country = 'JPN' |
When this query is used in the row filter, it selects only those customers whose country is listed as Japan. . | All customers must have at least one corresponding order. |
|
This query, when used as the pass condition, checks that each customer has at least one associated order. | After the rule is evaluated, an evaluated count is generated representing customers who belong to the country of Japan and a passed count is generated representing those customers who have at least one order. |
| Invoices must be older than 30 days . |
|
This query filters invoices that were created more than 30 days ago. | Invoices must have an associated payment record |
|
This query identifies invoices that have corresponding payment records. Invoices lacking payment records are treated as failed records. | After rule evaluation, an evaluated count is generated representing the number of records whose invoice is older than 30 days and a passed count is generated representing those records that have associated payment records. |