Rules for using EQL to define a view

The view definition EQL defines the content of the view. When defining a view, you need to keep the following restrictions in mind.

While the definition may contain multiple DEFINE and SELECT statements, the last statement must be a DEFINE statement that lists all of the view attributes.

The statement should include at least one grouping attribute.

Also, when defining a view:

Avoid aliasing attributes from the physical data.
For example, if the data source includes a "Region" attribute, then when you add the attribute to a view, define it as "Region".
```
DEFINE Region AS Region
```
Don't define the attribute under a different name, such as:
```
DEFINE Region AS RegionList
```
End users can only refine by attributes that are present in the physical data. If you alias an attribute, then end users cannot use it to refine the data.
Do not give a derived attribute the same name as an attribute from the physical data.
For example, if you are adding an attribute that averages the values of an existing "Sales" attribute, do not define the new attribute as:
```
DEFINE avg(Sales) as Sales
```
Instead, use something like:
```
DEFINE avg(Sales) as AvgSales
```
This is also to prevent end users from trying to refine by an attribute that is not in the physical data.
Avoid having a defining statement without a GROUP BY clause.
Studio uses the GROUP BY attributes as the identifying attributes for each record in the view.

If there are no GROUP BY attributes, then Studio cannot identify the identifying attributes, and the view cannot be used in a Record Details or Compare component.

Also note that because EQL does not support multi-assign, any multi-assign attribute that you add to a view will not be multi-assign in the context of the view. For additional information on using EQL, including the limitations on multi-assign, see the Oracle Endeca Server EQL Guide.

Here is an example of an EQL query for generating a list of products, including price and sales numbers, from a data source consisting of individual transactions.

DEFINE Products AS SELECT
ProductName AS ProductName,
ProductSubcategoryName AS ProductSubcategoryName,
ProductCategoryName AS ProductCategoryName,
Description AS Description,
avg("FactSales_SalesAmount") AS AvgSales,
sum("FactSales_SalesAmount") AS SalesSum,
avg(StandardCost) AS AvgStandardCost,
avg(ListPrice) AS AvgListPrice,
(AvgListPrice - AvgStandardCost) AS Profit
GROUP BY ProductName