Creating Your Query
Now that you know how to create queries, it’s time to put that knowledge to use. I am going to start the queries, and leave it to you to finish them:
<sql:SQLXML xmlns:sql="http://pilotfish.sqlxml">
<sql:Execute into="policies" as="policy">
<sql:SQL>
SELECT
*
FROM
SBLIWHMO.POLICY_RPT
WHERE
POLICY_STATUS_CODE like 'P%' AND
TRIM(POLICY_TYPE) != 'ANNUITY' AND
BRANCH_NUM not like '009%' AND
CVG_NUM = '01' AND
POLICY_ID = '344868875'
ORDER BY
BRANCH_NUM
</sql:SQL>
</sql:Execute>
Now we have a basic variable – the policy. Add the following:
<sql:Iterate over="policies" as="policy">
<sql:Execute into="policy.BRANCHRPT" 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>
<sql:XMLOut var="policies" />
</sql:SQLXML>
And we now have a coverage and its branch report(s). Now we need its client reports, its requirements, its agent reports, its product reports, and any additional coverages.
For reference, the execute into structures I used were as follows:
<sql:Execute into="policy.PRODUCTRPT" as="PRODUCTRPT"> </sql:Execute> <sql:Execute into="policy.REQUIREMENTS" as="REQUIREMENT"> </sql:Execute> <sql:Execute into="policy.CLIENTRPT" as="CLIENTRPT"> </sql:Execute> <sql:Execute into="policy.AGENTRPT" as="AGENTRPT"> </sql:Execute> <sql:Execute into="policy.COVERAGES" as="COVERAGE"> </sql:Execute>