SELECT clause

The SELECT clause defines the list of attributes on the records produced by the statement.

Its syntax is as follows:

SELECT <expression> AS <attributeKey>[, <expression> AS <key>]*

For example:

SELECT Sum(Amount) AS TotalSales

The attribute definitions can refer to previously-defined attributes, as shown in the following example:

SELECT Sum(Amount) AS TotalSales, TotalSales / 4 AS QuarterAvg

Note:

If an attribute defined in a SELECT clause is used in the statement's GROUP clause, then the expression can only refer to source attributes and other attributes used in the GROUP clause. It must not contain aggregations.

Using SELECT *

SELECT * selects all the attributes at once from a given record source. The rules for using SELECT * are:

You can use SELECT * over a collection. The statement's FROM clause specifies a named state (which in turn references a collection name). Keep in mind that retrieving all records from a very large collection can take some time.
You cannot use the AS clause with a SELECT * statement. For example, this returns an error:
```
SELECT * AS allRecs
```
You cannot use SELECT * in a grouping statement.
SELECT * expansion will include grouping keys that are defined by a LET clause in the source statement.

For example, assume this simple query:

DEFINE ResellerInfo AS
SELECT 
  DimReseller_ResellerName,
  DimGeography_StateProvinceName, 
  DimReseller_Phone
FROM SaleState;

RETURN Resellers as
SELECT *
FROM ResellerInfo

The query first generates an intermediate result (named ResellerInfo) from data in three attributes, and then uses SELECT * to select all the attributes from ResellerInfo.

The sample query selects all the attributes from a given collection:

RETURN Results as
SELECT *
FROM WineState

In the query, the WineState state references the Wines collection, which means that all of that collection's records are returned.

You can also use SELECT * with a JOIN clause, as shown in this example:

DEFINE Reseller AS
SELECT 
  DimReseller_ResellerKey,
  DimReseller_ResellerName,
  DimReseller_AnnualSales
FROM SaleState;

DEFINE Orders AS
SELECT 
  FactSales_ResellerKey,
  FactSales_SalesAmount
FROM SaleState;

RETURN TopResellers AS
SELECT
  R.*, O.FactSales_SalesAmount
FROM Reseller R JOIN Orders O on (R.DimReseller_ResellerKey = O.FactSales_ResellerKey)
WHERE O.FactSales_SalesAmount > 10000

In the example, the expression R.* (in the RETURN TopResellers statement) expands to include all the attributes selected in the DEFINE Reseller statement.

Note that you should be aware of the behavior of SELECT * clauses in regard to attributes with the same name in statements. That is, assuming this SELECT clause:

SELECT Amt, *

If * includes an attribute named Amt, then the SELECT will trigger the EQL error: "Attribute "Amt" is defined more than once."

Likewise in a join:

SELECT * FROM a JOIN b ON (...)

If a and b both contain an attribute with the same name, then the query triggers the same EQL error as above. It will list one of the attributes that the two sides of the join share. Note that the error message will reference the statement name with the problem.