Fetch Entities Criteria and SQL Equivalence

Overview

Fetch entities step is used to pull data that meets the Fetch criteria from the database. The Fetch criteria section allows users to input the criteria that the data must meet to be fetched. These criteria are equivalent to database SQL queries. This document explains which SQL operator these criteria are equivalent to.

Note

To use Fetch Entities to pull from a SQL database, users will need to select a a data type from Type Name, that corresponds with information in the database. See Fetch Entities for further info on this process.

Query Match Type SQL Equivalent

The "Query Match Type" section on a Fetch entities step contains the query operators that are used to fetch specific data that match that condition. In a SQL query statement, these operators would be used in the "WHERE clause". The table below lists the Query Match Types and their equivalence in SQL.

Query Match Type	SQL Equivalent	Example	Description
Equals	Equal (=)	SELECT (column_names) FROM (table) WHERE (column_names) = {value};	The "equal to" operator is used for equality tests within two expressions.
Contains	CONTAINS	SELECT * FROM(table) WHERE CONTAINS (column_names) , (text_Value);	"CONTAINS" operator does a full-text search on full-text indexed columns containing character-based data types.
DoesNotEqual	Not equal (<>, !=)	SELECT (column_names) FROM (table) WHERE (column_names) <> {value};	"Not equal" operator checks if two expressions are not equal.
GreaterThanOrEqualTo	Greater Than of Eqauls To (>=)	SELECT (column_names) FROM (table) WHERE (column_names) >= {value};	The "greater than equal to" operator is used to test whether an expression is either greater than or equal to another one.
LessThanOrEqualTo	Less Than of Eqauls To (<=)	SELECT (column_names) FROM (table) WHERE (column_names) <= {value};	The "Less than equal to" operator is used to test whether an expression is either greater than or equal to another one.
GreaterThan	Greater Than (>)	SELECT (column_names) FROM (table) WHERE (column_names) > {value};	The "Greater than" operator is used to test whether an expression is greater than another one.
LessThan	Less Than(<)	SELECT (column_names) FROM (table) WHERE(column_names) < {value};	The "Less than" operator is used to test whether an expression is less than another one.
Exists	EXIST	SELECT (column_names) FROM {table} WHERE EXISTS (subquery);	The "EXISTS" operator checks the existence of a result of a Subquery. It pulls data that is TRUE if the subquery returns one or more records.
DoesNotExist	NOT EXIST	SELECT (column_names) FROM (table) WHERE NOT EXISTS (subquery);	The " NOT EXISTS" operator is also used for existence determination but works opposite of EXIST operator. It will be TRUE if there are no records are returned.
IsNotNull	IS NOT NULL	SELECT column_names FROM table_name WHERE column_name IS NOT NULL;	"IS NOT NULL" operator tests for empty values.
IsNull	IS NULL	SELECT column_names FROM table_name WHERE column_name IS NULL;	The "IS NULL" operator tests for non-empty values.
Start With	LIKE Value%	SELECT * FROM (table) WHERE (Column) LIKE 'S%';	Finds any record that starts with the given value.
Ends With	LIKE %Value	SELECT * FROM (table) WHERE (Column) LIKE '%S';	Finds any record that ends with the given value.
DoesNotContain	NOT IN %value% NOT LIKE %value%	SELECT * FROM (table) WHERE (Column) NOT LIKE '%text%';	Finds records that do not contain a specific value.
DoesNotEndWith	NOT IN %value NOT LIKE %value	SELECT * FROM (table) WHERE (Column) NOT LIKE '%text';	Finds records that do not end contain a specific value.
DoesNotStartWith	NOT IN value% NOT LIKE value%	SELECT * FROM (table) WHERE (Column) NOT LIKE 'text%';	Finds records that do not start contain a specific value.
Input Data Alias	alias	SELECT column_name AS alias_name FROM table_name;	Gives input criteria a separate name from the default field name. Allows differentiation between criteria set on the same field.

Query Match Type

SQL Equivalent

Example

Description

Equals

Equal (=)

SELECT (column_names)

FROM (table)

WHERE (column_names) = {value};

The "equal to" operator is used for equality tests within two expressions.

Contains

CONTAINS

SELECT *
FROM(table)
WHERE CONTAINS (column_names) , (text_Value);

"CONTAINS" operator does a full-text search on full-text indexed columns containing character-based data types.

DoesNotEqual

Not equal (<>, !=)

SELECT (column_names)

FROM (table)

WHERE (column_names) <> {value};

"Not equal" operator checks if two expressions are not equal.

GreaterThanOrEqualTo

Greater Than of Eqauls To (>=)

SELECT (column_names)

FROM (table)

WHERE (column_names) >= {value};

The "greater than equal to" operator is used to test whether an expression is either greater than or equal to another one.

LessThanOrEqualTo

Less Than of Eqauls To (<=)

SELECT (column_names)

FROM (table)

WHERE (column_names) <= {value};

The "Less than equal to" operator is used to test whether an expression is either greater than or equal to another one.

GreaterThan

Greater Than (>)

SELECT (column_names)

FROM (table)

WHERE (column_names) > {value};

The "Greater than" operator is used to test whether an expression is greater than another one.

LessThan

Less Than(<)

SELECT (column_names)

FROM (table)

WHERE(column_names) < {value};

The "Less than" operator is used to test whether an expression is less than another one.

Exists

EXIST

SELECT (column_names)

FROM {table}

WHERE EXISTS (subquery);

The "EXISTS" operator checks the existence of a result of a Subquery. It pulls data that is TRUE if the subquery returns one or more records.

DoesNotExist

NOT EXIST

SELECT (column_names)

FROM (table)

WHERE NOT EXISTS (subquery);

The " NOT EXISTS" operator is also used for existence determination but works opposite of EXIST operator. It will be TRUE if there are no records are returned.

IsNotNull

IS NOT NULL

SELECT column_names

FROM table_name

WHERE column_name IS NOT NULL;

"IS NOT NULL" operator tests for empty values.

IsNull

IS NULL

SELECT column_names

FROM table_name

WHERE column_name IS NULL;

The "IS NULL" operator tests for non-empty values.

Start With

LIKE Value%

SELECT * FROM (table)

WHERE (Column) LIKE 'S%';

Finds any record that starts with the given value.

Ends With

LIKE %Value

SELECT * FROM (table)

WHERE (Column) LIKE '%S';

Finds any record that ends with the given value.

DoesNotContain

NOT IN %value%
NOT LIKE %value%

SELECT *
FROM (table)
WHERE (Column) NOT LIKE '%text%';

Finds records that do not contain a specific value.

DoesNotEndWith

NOT IN %value
NOT LIKE %value

SELECT *
FROM (table)
WHERE (Column) NOT LIKE '%text';

Finds records that do not end contain a specific value.

DoesNotStartWith

NOT IN value%
NOT LIKE value%

SELECT *
FROM (table)
WHERE (Column) NOT LIKE 'text%';

Finds records that do not start contain a specific value.

Input Data Alias

alias

SELECT column_name AS alias_name

FROM table_name;

Gives input criteria a separate name from the default field name. Allows differentiation between criteria set on the same field.

Fetch Entities Criteria and SQL Equivalence

Overview

Query Match Type SQL Equivalent

What's Next