By default, when processing user queries, table references in the SQL are always given alias names. Alias names are convenient shorthand for long table references, and they are required when trying to build correlated subqueries. These alias names take the form “ALn”, when the ‘n’ is replaced by an arbitrary number. These numbers are usually based on the topic priority properties of the data model and can easily change based on several factors. For example, a user with the proper permissions can rearrange the topics, thus giving them different priorities. Because these numbers are dynamic, constraint specifications should never rely on them. Instead, by using the naming scheme above, the appropriate alias will be added to the constraints. So, if the requirement is a correlated subquery, the appropriate name will be given to the column in the outer query when referenced by the correlated subquery.
In the example above, using a subquery to restrict STORE_ID values to those in a specific state, it was neither necessary nor desirable to use the Hyperion Solutions naming conventions. There, the set of values was to be derived in a subquery that operated independently of the main query. Consequently, the ‘From.’ was not used in the FROM clause of the subquery, and the alias names were given in a way to not conflict with the alias names generated automatically by the software.
To use a correlated subquery, then, consider syntax like the following:
…FROM STORES S WHERE S.STORE_ID = Stores.Store_Id
The reference to the right of the equal sign will pick up the alias name from the outer query and thus provide the correct correlation requirements.