Subvariables

Relational databases are, of course, relational – and the problem with the above structures is that they would seem to assume one-to-one relationships on all data. Subvariables are utilized to represent information for one-to-many relationships. (Many-to-many relationships will require careful planning.)

<sql:Iterate over="policies" as="policy">
        <sql:Execute into="policy.BRANCHRPTS" as="BRANCHRPT">
                <sql:SQL>
                        SELECT
                                *
                        FROM
                                SBLIWHMO.BRANCH_RPT a
                        WHERE
                                a.BRANCH_NUM IN
(
select
p.branch_num
from
SBLIWHMO.POLICY_RPT p
where
p.POLICY_ID = ?
)
                </sql:SQL>
                <sql:Params>ognl:#policy.getFieldValue('POLICY_ID')</sql:Params>
        </sql:Execute>
</sql:Iterate>

Above, we have an example of an insertion into a Subvariable – it is as simple as utilizing dot notation when specifying where the contents of a query are to be placed. In the XML, for each “Policy” element, the result of this will be a “BRANCHRPTS” element, which will contain each of the “BRANCHRPT” elements which the query above has generated.