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. |
IS_DESCENDANT(attribute, string) | Include the record if the named attribute is the attribute specified or a descendant. 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. |
IS_ANCESTOR(attribute, string) | 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. |
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. |
In the first 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')
In the second 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
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.