Fetch Entities Criteria and SQL Equivalence
  • 13 Apr 2022
  • 2 Minutes to read
  • Dark
    Light

Fetch Entities Criteria and SQL Equivalence

  • Dark
    Light

Overview

A Fetch Entities (Database Entity) step is used to pull data from the database that matches the Fetch Criteria inputThe Fetch Criteria section allows users to input the criteria that the data must meet to be fetched and are equivalent to SQL queries within a database. 

To use Fetch Entities to pull from a SQL database, users will need to select a previously created data type from the Type Name field under the ENTITY FETCH DEFINITION category that corresponds with the information that will be searched in the database. For more information, see Retrieving Entities

Query Match Type SQL Equivalent

The Query Match Type field contains the query operators that are used to fetch specific data that match a specified condition. In a SQL query statement, these operators are used in the "WHERE" clause. The table below lists the Query Match Types, their equivalence in SQL, and an example.

Query Match TypeSQL EquivalentExampleDescription
EqualsEqual (=)SELECT (column_names)
FROM (table)
WHERE (column_names)  = {value};
Used for equality tests within two expressions.
ContainsCONTAINSSELECT *
FROM(table)
WHERE CONTAINS (column_names) , (text_Value);
Does a full-text search on full-text indexed columns containing character-based data types.
DoesNotEqualNot equal (<>, !=)

SELECT (column_names)
FROM (table)
WHERE (column_names)  <> {value};

Checks if two expressions are not equal.
GreaterThanOrEqualToGreater Than or Equals To (>=)

SELECT (column_names)
 FROM (table)
WHERE (column_names)  >= {value};
Used to test whether an expression is either greater than or equal to another one.
LessThanOrEqualToLess Than or Equals To (<=)

SELECT (column_names) 

 FROM (table)

WHERE (column_names)  <= {value};


Used to test whether an expression is either greater than or equal to another one.
GreaterThanGreater Than (>)SELECT (column_names)
 FROM (table)
WHERE (column_names)  > {value};
Used to test whether an expression is greater than another one.
LessThanLess Than(<)
SELECT (column_names)
 FROM (table)
WHERE(column_names)  < {value};
Used to test whether an expression is less than another one.
Exists


EXIST



SELECT (column_names)
 FROM {table}
WHERE EXISTS (subquery);
Checks the existence of a result of a Subquery. It pulls data that is TRUE if the subquery returns one or more records.
DoesNotExistNOT EXISTSELECT (column_names)
FROM (table)
WHERE NOT EXISTS (subquery);
Used for existence determination but works opposite of EXIST operator. It will be TRUE if there are no records are returned.
IsNotNullIS NOT NULLSELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
Tests for empty values.
IsNullIS NULLSELECT column_names
FROM table_name
WHERE column_name IS NULL;
Tests for non-empty values.
StartsWithLIKE Value%SELECT * FROM (table)
WHERE (Column) LIKE 'S%';
Finds any record that starts with the given value.
EndsWithLIKE %ValueSELECT * FROM (table)
WHERE (Column) LIKE '%S';
Finds any record that ends with the given value.
DoesNotContainNOT IN %value%
NOT LIKE %value%
SELECT *
FROM (table)
WHERE (Column) NOT LIKE '%text%';
Finds records that do not contain a specific value.
DoesNotEndWithNOT IN %value
NOT LIKE %value
SELECT *
FROM (table)
WHERE (Column) NOT LIKE '%text';
Finds records that do not end contain a specific value.
DoesNotStartWithNOT 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 aliasSELECT 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. 
IsInListINSELECT *
FROM (table)
WHERE column_name IN (value1, value2)
Finds records that match to any of the specified values
NotInListNOT IN SELECT *
FROM (table)
WHERE column_name NOT IN (value1, value2)
Finds records that do not match any of the specified values



For further information on Flows, visit the Decisions Forum.

Was this article helpful?