Hierarchy functions

EQL supports hierarchy functions on managed attributes.

You can filter by a descendant or an ancestor, or return a specific or relative level of the hierarchy. Managed attributes can be aliased in the SELECT statement and elsewhere.

The following are the related functions:

Function	Description
`ANCESTOR(expr, int)`	Return the ancestor of the named attribute at the depth specified. Returns NULL if the requested depth is greater than the depth of the attribute value. The root is at depth 0.
`HIERARCHY_LEVEL(expr)`	Return the level of the named attribute as a number. The level is the number of values on the path from the root to it. The root is always level 0.
`TO_MANAGED_VALUE(attribute, value)`	Returns a managed value literal from literals representing a managed attribute and a managed value. Both parameters must be string literals.
`IS_DESCENDANT(attribute, value)`	Include the record if the named attribute is the attribute specified or a descendant and if the specified value matches. If the attribute is not a member of the specified hierarchy, it is a compile-time error. If no attribute with the primary key in the attribute is found, it results in NULL. This function can also be used with standard attributes. In this case, the record is included if the specified attribute exists and the specified value matches.
`IS_ANCESTOR(attribute, value)`	Include the record if the named attribute is the attribute specified or an ancestor. If the attribute is not a member of the specified hierarchy, it is a compile-time error. If no attribute with the primary key in the attribute is found, it results in NULL. This function can also be used with standard attributes. In this case, the record is included if the specified attribute exists and the specified value matches.
`GET_LCA(attribute)`	A row function that returns the LCA (least common ancestor) of the two managed attributes. The two managed attributes should belong to same hierarchy. Otherwise, it is a compile-time error.
`LCA(attribute)`	An aggregation function that returns the LCA of the managed attributes in the specified attribute column. The LCA is the lowest point in a hierarchy that is an ancestor of all specified members. Any encountered NULL values are ignored by the function.

Hierarchy examples

Example 1: In this example, we filter by product category CAT_BIKES, and get all records assigned produce category CAT_BIKES or a descendant thereof:

RETURN example1 AS
SELECT
  ProductCategory AS ProductCategory,
  ANCESTOR(ProductCategory, 0) AS Ancestor
;
RETURN example2 AS
  ProductCategory AS ProductCategory,
  ANCESTOR(ProductCategory, HIERARCHY_LEVEL(ProductCategory)-1) AS Ancestor
WHERE
  IS_DESCENDANT(ProductCategory, 'CAT_BIKES')

Example 2: In this example, we want to return level 1 (one level below the root) of the Product Category hierarchy:

RETURN Results AS
SELECT
	 ProductCategory AS PC,
	 ANCESTOR(PC, 1) AS Ancestor 
WHERE
	 ANCESTOR(ProductCategory, 1) = 'CAT_BIKES'
GROUP BY PC
ORDER BY PC

Example 3: In the third example, we want to return the direct ancestor of the Product Category hierarchy:

RETURN Results AS
SELECT
	 ProductCategory AS PC,
	 ANCESTOR(PC, HIERARCHY_LEVEL(PC) - 1) AS Parent
WHERE
	 ANCESTOR(ProductCategory, 1) = 'CAT_BIKES'
GROUP BY PC
ORDER BY PC

In the second and third examples, we use GROUP BY to de-duplicate. In addition, note that even though we aliased ProductCategory AS PC, we cannot use the alias in the WHERE clause, because the alias does not become available until after WHERE clause executes.

Note: GROUP BY statements cannot use the ANCESTOR function, because you cannot group by an expression in EQL.

Example 4: This abbreviated example shows the use of the TO_MANAGED_VALUE function:

RETURN Results AS
SELECT
  HIERARCHY_LEVEL(TO_MANAGED_VALUE('ProductCategory', 'Bikes')) AS HL
...