Show TOC

Syntax documentation*JOIN Locate this document in the navigation structure

Allowed uses: By Commit, SQL

*JOIN( {tablename} , {dimension.property} [, {tablefield}] [, {selected fields}])

Where

Is

{tablename}

The name of the user-defined table.

{dimension.property}

The dimension and property that should be used to join the table with the Planning and Consolidation data.

{tablefield}

The field in the selected tables on which the dimension property is joined.

{selected fields}

The fields in the table that are of interest in the current logic. If more than one, the list of fields must be enclosed in double quotes.

With this instruction the rules can be linked to the values entered in some field of a user-defined table.

Example Example

*JOIN(RulesTable, Account.Rule, Rule, "Destacc, Factor")

End of the example.

The above instruction tells the rules to join the Planning and Consolidation data with the data contained in a table called Consolidation Rules. This performs a join of the property Rule of the account dimension and the field Rule of the RulesTable table. The fields to read in the RulesTable are DestAcc and Factor. Behind the scenes, the generated SQL query looks more or less as follows:

select

….., mbrACCOUNT.[RULE] AS [ACCOUNT.RULE],

RULESTABLE.[DestAcc] AS [RULESTABLE.DestAcc],

RULESTABLE.[Factor] AS [RULESTABLE.Factor]

INNER JOIN RULESTABLE on mbrACCOUNT.[RULE] = RULESTABLE.[RULE]

The parameters {tablefield} and {selected fields} are optional. If omitted, the {tablefield} is assumed to be the same as the property of the joined dimension, and the {selected fields} are assumed to be all fields in the table. In other words, the above example could have been written as follows:

*JOIN(RulesTable, Account.Rule)

With the above technique, an account could be assigned a rule of behavior using a regular property. A separate table of Rules could then be maintained, defining in a generic way what a rule should imply in the execution of a rule.

Once the join has been defined, all fields in the joined table can be used anywhere in the WHEN/ENDWHEN statement to perform the appropriate calculations.

Example Example

*JOIN(AccountRules,account.rule)

*WHEN *

*IS *

*REC(FACTOR= AccountRules.Factor, ACCOUNT=AccountRules.DestAcc)

*ENDWHEN

End of the example.

By nature a join only accepts the values of the selected Planning and Consolidation region of data that have in the selected property a value corresponding to the joined field in the joined table. In other words, still referring to the above example, all records not having a valid Rule are skipped, even if included in the selected region. This could also be considered as a way to filter the members of a dimension without using a XDIM_MEMBERSET instruction.