Endeca Server - Endeca Query Language (EQL)

About

EQL is the Endeca Query Language that permits you to retrieve data.

Articles Related

Syntax

Simple EQL statements can be broken down into five primary components:

/* Comments */ 
RETURN
SELECT
[Expressions]
GROUP BY
ORDER BY�

Clauses are not case-sensitive Standard and managed attribute references are case-sensitive

Words

• WHERE
• HAVING
• IN
• DEFINE
• Lookups
• FROM
• coalesce
• GROUP BY hierarchy
• PAGE
• PERCENTILE
• CASE
• JOIN

RETURN

Provides the key for accessing results from the Oracle Endeca Server query result

RETURN "MyExample" AS SELECT COUNT(1) AS �"TotalCount” GROUP

SELECT

Specifies one or more expressions to evaluate for an EQL statement

SELECT <expr1> AS <key1>, <expr2> AS <key2>…

GROUP

“Group” define the buckets.

RETURN "MyExample" AS 
SELECT �
   AVG("Gross") AS "AvgGross", 
   SUM("Gross") AS "TotalGross" 
GROUP 

RETURN "Example" AS 
SELECT �
    AVG("Gross") AS "AvgGross", �
    SUM("Gross") AS "TotalGross" 
�GROUP BY “Regions”

Aggregate function

DEFINE

Used when statement is only intended as input for another statement

FROM

Allows a previous DEFINE statement’s results to be the current statement’s input record set.

Essentially, FROM allows EQL statements to be nested (for instance, in the case of a view)

Without a FROM component, an EQL statement will always refer to the base record set returned by the Navigation query.

Note that you may only have one FROM per query.

The default FROM is the current navigation state.

DEFINE "Customers" AS SELECT 
	ARB("Cases Produced") AS "CasesProduced" 
	GROUP BY "Customer Id";

RETURN "Example" AS SELECT 
	AVG("Customers"["CustomerId"]."Cases Produced")
	AS "AvgCasesProduced" FROM "Customers" 
	GROUP

WHERE

WHERE Allows filtering of records prior to analysis and can be either per-expression (Per Attribute) or global (for the whole query). The placement of the “WHERE” word before or after the “AS” word determines the scope.

• Per-Expression (Per Attribute)

Return statement as select
sum(FactSales_SalesAmount) where (DimDate_FiscalYear=2008) as Sales2008,
sum(FactSales_SalesAmount) where (DimDate_FiscalYear=2007) as Sales2007,
((Sales2008-Sales2007)/Sales2007 * 100) as pctChange,
countDistinct(FactSales_SalesOrderNumber) as TransactionCount
group

• Global (for the whole query)

RETURN QuarterTotals AS SELECT 
SUM("Gross") AS "SalesTotal"
WHERE "Sales Managers"='Titus Arden'
GROUP BY BookingQuarter"

Global WHERE clauses are not frequently used, since Navigation or Record Filters can be used instead. 

HAVING

Allows filtering of records after analysis (grouping)

RETURN "Example" AS SELECT 
	"Customer Id" AS "Customer Id", 		
		AVG ("Gross") AS "AvgGross", 
		SUM ("Gross") AS "TotalGross" 
GROUP BY "Customer Id"
HAVING "TotalGross" > 1000000 
ORDER BY "TotalGross" DESC

IN

Is used as a filter in either the WHERE or HAVING clause

DEFINE TopFive AS SELECT 
   SUM ("Gross") AS "TotalSalesPerCustomer"
   GROUP BY "CustomerId"
   ORDER BY "TotalSalesPerCustomer" DESC�   PAGE (0,5);

RETURN Others AS SELECT 
   SUM ("Gross") AS "TotalSales"
   WHERE NOT ["CustomerId"] IN TopFive 
   GROUP

Lookups

Provide the ability to refer to derived values in record sets that have been previously computed. Lookups can only refer to an EQL statement that was grouped and whose GROUP BY list matches the Key list indicated between the brackets.

Usage:

StatementName[KeyList].DerivedProp

Below: “Totals”[Regions].“TotalSales” is a lookup.

DEFINE "Totals" AS SELECT 
SUM("Gross") AS "TotalSales",
GROUP BY Regions;

RETURN "TotalSalesPerRegion" AS SELECT 
SUM("Gross") AS "TotalSalesPerRegion", 
("TotalSalesPerRegion" /"Totals"[Regions]."TotalSales") * 100 AS PctOfTotal
GROUP BY "Regions", "Varietals"

COALESCE

COALESCE returns the first non-null value from a provided list of expressions.

RETURN "Total Sales by Region" 
AS SELECT 
SUM(COALESCE("Audit Sales","Sales","0.0")) AS "TotalSales" GROUP BY Region

The COALESCE expression allows for user-specified null-handling. You can use COALESCE to evaluate records for multiple values and return the first non-null value encountered, in the order specified. The following requirements apply:

• You can specify two or more arguments to COALESCE
• Arguments that you specify to COALESCE should all be of the same type
• The types of arguments that COALESCE takes are: integer, integer64, double and string
• You cannot specify managed attributes as arguments to COALESCE. However, you can specify a standard attribute as arguments to COALESCE resulting in a valid query.
• The COALESCE expression can only be used in a SELECT clause, and not in other clauses (such as WHERE)

CASE

If no condition matches, result is NULL

RETURN Result AS SELECT 
   CASE 
	WHEN (AgeInWeeks = 0) THEN 'Past 7 Days' 
	WHEN (AgeInWeeks = 1) THEN 'Prior 7 Days' 
	ELSE 'Other' 
   END AS “Label”,
   count(1) as RecordsCount
Group By Label 

Operator

Mathematical

+ , - , * , /

Logical

• OR
• AND
• =
• >
• <
• <=
• >=
• IS NULL

Quotes

Double

When referring to a Key, whether it is an original attribute, a derived attribute, or a reserved word.

SELECT "orderSize" AS "size" 
WHERE "orderSize" > '2'
GROUP BY "orderSize"

Single

When referring to a Literal, whether it is a string constant or a number.

WHERE "orderSize" > '2'
WHERE "companyName" = 'IBM'
SELECT 'Others' AS "Name" 

None

Double quotes can be left off when the Key is a single word. One exception is if that word is reserved (e.g. Year).

• ALLOWED:

SELECT "Order Size" AS size
SELECT P_Year AS "Year"

• NOT ALLOWED:

SELECT Order Size AS "size"
SELECT "P_Year" AS Year

Single quotes can only be left off for a number.

• ALLOWED:

WHERE "Order Size" > 2
SELECT 100 AS "Number"

• NOT ALLOWED:

WHERE "companyName" = IBM
SELECT Others AS "Name"

Order of Processing

Studio EQL-enabled components

There are six Studio EQL-enabled components:

• Alerts
• Chart
• Cross Tab
• Map
• Metrics Bar
• Results Table

Documentation / Reference

• Endeca Server EQL Guide