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
BedrockMission!

Learn More

View all

Sign in to view all badges

QUERYDEF with subquery dependent on main query

Avatar

Avatar
Validate 25
Level 4
salvdangelo
Level 4

Likes

45 likes

Total Posts

130 posts

Correct Reply

4 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Boost 5
Boost 3
View profile

Avatar
Validate 25
Level 4
salvdangelo
Level 4

Likes

45 likes

Total Posts

130 posts

Correct Reply

4 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Boost 5
Boost 3
View profile
salvdangelo
Level 4

03-04-2018

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

Replies

Avatar

Avatar
Give Back 50
Level 10
florentlb
Level 10

Likes

236 likes

Total Posts

1,109 posts

Correct Reply

239 solutions
Top badges earned
Give Back 50
Give Back 5
Give Back 3
Give Back 25
Give Back 200
View profile

Avatar
Give Back 50
Level 10
florentlb
Level 10

Likes

236 likes

Total Posts

1,109 posts

Correct Reply

239 solutions
Top badges earned
Give Back 50
Give Back 5
Give Back 3
Give Back 25
Give Back 200
View profile
florentlb
Level 10

18-04-2018

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