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