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.