Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

The 4th edition of the Campaign Community Lens newsletter is out now!

QUERYDEF with subquery dependent on main query


Level 5


I am trying to develop this query with javascript:

"Sum all the @amount of the orders done in a period (StartCheckDate, EndCheckDate) by the customers that have received the current campaign/operation. Note that the order must be done after the customer has received the email"

var xmlTotalAmount = <queryDef schema="tods:receiptLine" operation="select">


  <node alias="@totalAmount" expr="sum(@amount)"/>



  <condition expr={ '[receipt-receiptLine/@issueDate] >= #' + startCheckDateFormatted + '#' }/>

  <condition expr={ '[receipt-receiptLine/@issueDate] <= #' + endCheckDateFormatted + '#' }/>

  <condition setOperator="IN" expr="[receipt-receiptLine/@crmId]">

     <subQuery schema="nms:broadLogRcp">


          <node expr="[recipient/@crmId]" />



          <condition expr={ '[delivery/operation/@id] = ' + op.@id }/>

          <condition expr={ '@status = 1'}/>

          <condition expr={'@eventDate < ???'}.






I cannot express the condition written in red since the @issueDate belongs to the main query.

Any idea?
In SQL should be simple with label notation, but here is an issue for me.

Thanks in advance,


@Jean-Serge Biron

Adobe Campaign

1 Reply


Level 10

Hi Slavatore,

I don't know the answer but maybe you can achieve that using a regular query through the UI and by checking the generated SQL code after that?