can I reference sql out put as parameter in 2nd query in xml publisher

Question

I need to reference the sql data of one query as parameter value in 2nd query.

for example:

select order_no, order_date, customer
from orders;

select payment_no, payment_date, amount
from payments
where order_no = [:order_no];

is this possible in XML?

Answer 1

here is the solutions.

Method 1 Bind Variables:

<dataQuery>
 <sqlStatement name="Q1">
 <![CDATA[ 
    SELECT order_no, order_date, customer from orders 
 ]]> 
 </sqlStatement>
 <sqlStatement name="Q2">
  <![CDATA[ 
     SELECT  payment_no, payment_date, amount
     from payments where order_no = :order_no ]]> 
 </sqlStatement>
</dataQuery>

we just need to reference using colon (:) in 2nd query.

Method 2 Query Link:

 <dataQuery>  
<sqlStatement name="Q1">  
<![CDATA[ 
     SELECT order_no, order_date, customer from orders  ]]>   
</sqlStatement>  
<sqlStatement name="Q2">   
<![CDATA[ 
      SELECT  payment_no, payment_date, amount
          from payments ]]>   
</sqlStatement> 
<link name="ORDER_LINK" parentQuery="Q1" parentColumn="ORDER_NO" childQuery="Q_2" childColumn="ORDER_NO"/> 
</dataQuery>