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

QUERYDEF with subquery dependent on main query

salvdangelo
Level 5
Level 5

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

1 Reply
florentlb
Level 10
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?

Florent