SELECT clauses

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 cannot use SELECT * over the corpus. This means that you must use a FROM clause in your statement to reference a non-corpus source.
  • You cannot use SELECT * in a grouping statement.
For example, assume this simple query:
DEFINE ResellerInfo as
SELECT DimReseller_ResellerName, DimGeography_StateProvinceName, DimReseller_Phone;

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.

You can also use SELECT * with a JOIN clause, as shown in this example:
DEFINE Reseller AS
SELECT DimReseller_ResellerKey, DimReseller_ResellerName, DimReseller_AnnualSales;

DEFINE Orders AS
SELECT FactSales_ResellerKey, FactSales_SalesAmount;

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 you should be aware of the behavior of SELECT * clauses in regard to attributes with the same name in statements. That is, assuming these scenarios:
SELECT Amt AS Z, *
   or
SELECT *, Amt AS Z
if * includes an attribute named Z, then whichever attribute comes first is the one included in the result.
Likewise in a join:
SELECT * FROM a JOIN b ON (...)

If a and b both contain an attribute with the same name, then you get the attribute from the first statement in the JOIN clause.