In a logical view, the column objects are matched to columns returned by the SQL statement by name, not by position. The logical column objects may appear in any order, as long as the names match the column names or column aliases in the SQL statement.
This is a natural consequence of the way logical views are rendered into Strategy report SQL. Logical views are embedded in the SQL as either derived tables (also known as in-line views) or common table expressions. A derived table is an independent SELECT statement that appears enclosed in parentheses in the FROM clause.
For instance, consider a logical view defined as follows:
Note that the description column is listed first, even though it appears second in the SQL statement.
A report with the C attribute produces SQL as follows against a SQL Server warehouse:
select a11.C_ID C_ID,
max(a12.C_DESC) C_DESC,
sum(a11.Fact) WJXBFS1
from Fact2 a11
join (select distinct C_ID, C_DESC
from DIM_ABC) a12
on (a11.C_ID = a12.C_ID)
group by a11.C_ID
The logical view produces a virtual table in the database with two columns: C_ID and C_DESC. Any other clause in the query may identify these columns by name, just as any column in a physical table is identified by name.
For this reason, Strategy does not need to be concerned with the order of the columns in the logical view SQL statement. It matters only that the columns are defined and that the SQL statement provides an unambiguous name for each column.
This is in contrast to a Strategy Freeform SQL report, in which result columns coming back from the query are mapped onto report objects by position. The difference is that in a logical view, the mapping takes place entirely on the database, while in a Freeform SQL report, Strategy has to interpret the dataset coming back from the database.
Following is logical view definition that is syntactically correct for the database (SQL Server), but which is invalid for Strategy reporting:
The reason is that the expression "cast(C_ID as char(3)) + ':' + C_DESC" does not have a column alias. That column will be assigned a name by the database. When the logical view appears in a query, though, the Strategy Engine will try to address that column by the name C_LONGDESC, which does not exist in the derived table. Any report trying to use the column will therefore return an ODBC error stating that C_LONGDESC could not be found.
For this reason, any calculated expression in a logical view SQL statement must have an explicit column alias, and that name must be used to identify the logical column object.
