Log out?

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>

    to post comments

    This is a unique website which will require a more modern browser to work! Please upgrade today!

    Thanks!

    Our editors are notified.

    Close