Oracle BI Publisher Blog

Bonjour mes ami!

I have just got back from a conference in the warmer climes of the French Riviera. Its been quite a shock; after nearly a week of mid 60s temperatures and blue seas coming back to somewhat chilly and mid 30s Colorado has taken some getting used to again. Thankfully the promised thaw is upon us and the piles of snow in my yard are now dwindling, much to my sons' chagrin the snowboard run we had in our back yard is becoming a distant memory. Ahh c'est la vie ... so to another article in the fragmented data template series.

I have spent some time looking at our documentation on data templates, its pretty good so please check it out. Im picking on a series of some of the tougher features to get your head around. for this article its creating and joining multiple queries.

There are two methods to join queries, lets assume we have the following queries:

SELECT DEPTNO,
DNAME,
LOC 
FROM DEPT
ORDER BY DEPTNO

SELECT  DEPTNO,
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
NVL(COMM,0) COMM
FROM EMP

we want to create a data template that will use the DEPT query as the master and the EMP as the child. We need to link the two queries.

Method 1: Query Link

We can create a link similar to Oracle Reports, like this:

<link name="DEPTEMP_LINK" parentQuery="Q1" parentColumn="DEPTNO" childQuery="Q_2" childColumn="DEPTNO"/>

We name the link and then specify the queries and the linking columns, so the <dataQuery> section of our data template will look like:

<dataQuery>
 <sqlStatement name="Q1">
 <![CDATA[ 
    SELECT DEPTNO,DNAME,LOC from scott.dept 
    order by deptno ]]> 
 </sqlStatement>
 <sqlStatement name="Q2">
  <![CDATA[ 
     SELECT  DEPTNO, EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,nvl(COMM,0) COMM
     from scott.EMP ]]> 
 </sqlStatement>
<link name="DEPTEMP_LINK" parentQuery="Q1" parentColumn="DEPTNO" childQuery="Q_2" childColumn="DEPTNO"/>
</dataQuery>

It works fine but we have to duplicate the DEPTNO column in the child query. Plus on a more real world query its going to be slower than taking ...

Method 2: Bind Variables

We did alot of testing on the joining of queries and found that usng bind variables was consistently faster then query links, so if you can, and there should not be a reason why you should not, use binds. What do I mena by bind variables, well taking our two queries we are able to have a master-detail relationship between the two i.e. the dept based query is the master and the emp the detail. We can join them quite easily using 'dept_id' as our joingin criteria:

<dataQuery>
 <sqlStatement name="Q1">
 <![CDATA[ 
    SELECT DEPTNUM,DNAME,LOC 
 from scott.dept 
    order by deptno ]]> 
 </sqlStatement>
 <sqlStatement name="Q2">
  <![CDATA[ 
     SELECT  DEPTNO, EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,nvl(COMM,0) COMM
     from scott.EMP
 where DEPTNO = :DEPTNUM ]]> 
 </sqlStatement>
</dataQuery>

Notice, I have changed the column names a little just to make the join clearer (dont cut 'n' paste this one.) We use :DEPTNUM from the first query in the where clause of the second, et voila we have two queries joined. Its simpler, less typing and even better its faster!
Salut!