Multiple Queries in Data Templete

Problem – Oracle report builder provides flexibility to create multiple queries and join those with common attribute. How does data template method help us to use multiple queries?

Solution – Data template also provides same flexibilities to list down queries in multiple sections and connect them as parent child relationship.

There are two approached available to join multiple queries in data template.

•        Bind variable
•        Data link method

Bind Variable most efficient and faster option. Identify the common column in parent query and use it as bind variable in child queries.

<dataQuery>

<sqlstatement name="Q1_USER">

<![CDATA[

SELECT user_id BIND_USER_ID ,user_name From fnd_user

]]>

</sqlstatement>

<sqlstatement name="Q2_RESP">

<![CDATA[

SELECT responsibility_id  From fnd_user_resp_groups WHERE user_id=: BIND_USER_ID

]]>

</sqlstatement>

</dataQuery>

Data Link is another way to establish the relationship between multiple queries

<link name="USER_RESP_LINK" parentQuery="Q1_USER" parentColumn="user_id" childQuery="Q2_RESP" childColumn="user_id" condition="Q1_USER.user_id= Q2_RESP.user_id" />

XML Output

<?xml version="1.0" encoding="UTF-8" ?>

< XXXOALEARNING >

<P_CUSTOMER/>100 </P_CUSTOMER/>

<LIST_G_USER>

<G_USER>

  <USERID>100</USERID>

  <USERNAME>Sandeep</USERNAME>

            <LIST_G_RESP>

            <G_RESP>

            <RESPONSIBILITY_ID>1010</ RESPONSIBILITY_ID >               

            <RESPONSIBILITY_ID>1020</ RESPONSIBILITY_ID >               

</G_RESP>

<LIST_G_RESP>

</G_USER>

<G_USER>

  <USERID>200</USERID>

  <USERNAME>Deepak</USERNAME>

            <LIST_G_RESP>

            <G_RESP>

            <RESPONSIBILITY_ID>1010</ RESPONSIBILITY_ID >               

            <RESPONSIBILITY_ID>1030</ RESPONSIBILITY_ID >               

</G_RESP>

<LIST_G_RESP>

</G_USER>

</LIST_G_USER>

</ XXXOALEARNING >