QUERYDEF with subquery dependent on main query

[salvdangelo]

Hi,

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">

<select>

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

</select>

<where>

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

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

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

     <subQuery schema="nms:broadLogRcp">

        <select>

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

        </select>

         <where>

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

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

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

        </where>

     </subQuery>

  </condition>

</where>

</queryDef>

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,
Salvatore

dattarays70361342​

@Jean-Serge Biron

Adobe Campaign​

[florentlb]

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?

Florent