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

SOLVED

[AC Classic] Migration of SQLData

arouillard44
Level 4
Level 4

Hello guys,

i'm back with a touchy question

I'm migrating a customer plateform from V5 to V6 and V7, I have have some problem with SQLData request transformation.

As explained in the documentation I need to modify these request because it's not accepted in the new version.

My SQLDATA request is contained in a Typology Rule, used by every campaign, so I need to fix it.

This is my SQLData statement :

  <where filterName="backGroundFilterFrm" filteringSchema="nms:recipient" id="13601463290">

    <condition boolOperator="OR" expr="subscription" internalId="1360146501" setOperator="NOT EXISTS">

      <condition boolOperator="AND" expr="@yrOptinTypeID = 2" internalId="1360146504"/>

      <condition boolOperator="AND" expr="[@service-id] IN (1127232 , 1127236)" internalId="1360146507"/>

      <condition expr="@yrConsentTypeID = 2" internalId="1360146510"/>

    </condition>

    <condition boolOperator="OR" expr="subHisto" internalId="1360146573" setOperator="EXISTS">

      <condition boolOperator="AND" expr="@CreatDate IN ([SQLDATA[(SELECT MAX(T.TSCREATED) FROM NmsSubHisto T WHERE T.iServiceId = 1127236 AND T.iRecipientId = S1.iRecipientId GROUP BY T.iRecipientId)]])"

                 internalId="1360146628"/>

      <condition boolOperator="AND" expr="@action = 0" internalId="1360146796"/>

      <condition boolOperator="AND" expr="[@service-id] = 1127236" internalId="1360146883"/>

      <condition expr="@yrConsentTypeID = 1" internalId="1360146898"/>

    </condition>

  </where>

So I tryed to transform the condition balisis in :

<condition setOperator="IN" expr="@CreatDate">

  <subQuery schema="nms:subHisto">

     <select>

       <node expr="Max([@CreatDate])" />

     </select>

     <where>

       <condition expr="[@service-id]=1127236"/>

     </where>

<groupBy>

<node expr="[@recipient-id]"/>

</groupBy>

   </subQuery>

</condition>

But It didn't worked.

Can someone help me to have the right format ?

Thanks by advance !

Poke Adhiyan

Poke florentlb

Poke Amit Kumar
Poke Vipul Raghav

Thanks team

1 Accepted Solution
arouillard44
Correct answer by
Level 4
Level 4

Hello guys,

I did the modification and still not working

1512337_pastedImage_3.png

This is the graphic result I have in my typology rule request window ;

1512341_pastedImage_4.png

But when i'm doing a data preview:

1512343_pastedImage_6.png

I don't get how to construct my subRequest without the SQLData !

Thanks by advance,

Regards,

View solution in original post

0 Replies
Jean-Serge_Biro
Community Advisor
Community Advisor

Bonjour Alexandre,

Stp peux-tu regarder cette doc sur le remplacement de SQLData :

Paramétrages généraux

Cdt

JS

arouillard44
Level 4
Level 4

Bonjour Jean-Serge,

Tu te doutes bien que j'ai déjà regardé cette documentation. D'ailleurs, si tu regardes, c'est cette syntaxe que je respecte et que j'essaye d'appliquer, mais sans succès...

C'est pour ca que je demande votre aide sur la modification que je dois apporter pour cette requête sur ma règle de typologie.

Alexandre

Jean-Serge_Biro
Community Advisor
Community Advisor

Alexandre,

La colonne SQL tsCreated correspond à l'élément @date dans le schéma nms:histo, et non pas @CreatDate.

Cdt

JS

Amit_Kumar
Community Advisor
Community Advisor

<where filterName="backGroundFilterFrm" filteringSchema="nms:recipient" id="13601463290">

    <condition boolOperator="OR" expr="subscription" internalId="1360146501" setOperator="NOT EXISTS">

      <condition boolOperator="AND" expr="@yrOptinTypeID = 2" internalId="1360146504"/>

      <condition boolOperator="AND" expr="[@service-id] IN (1127232 , 1127236)" internalId="1360146507"/>

      <condition expr="@yrConsentTypeID = 2" internalId="1360146510"/>

    </condition>

    <condition boolOperator="OR" expr="subHisto" internalId="1360146573" setOperator="EXISTS">

<condition boolOperator="AND" setOperator = "IN"  expr="@created" internalId="1360146628">

  <subQuery schema="nms:subHisto">

<select>

   <node expr="Max([@date])" />

</select>

<where>

   <condition expr="[@service-id]=1127236"/>

</where>

<groupBy>

<node expr="[@recipient-id]"/>

</groupBy>

  </subQuery>

</condition> 

      <condition boolOperator="AND" expr="@action = 0" internalId="1360146796"/>

      <condition boolOperator="AND" expr="[@service-id]=1127236" internalId="1360146883"/>

      <condition expr="@yrConsentTypeID = 1" internalId="1360146898"/>

    </condition>

</where>

it should work.

arouillard44
Level 4
Level 4

Hello guys !

Thanks for your advices !

I'm trying it and come back to you ASAP !

Regards,

arouillard44
Correct answer by
Level 4
Level 4

Hello guys,

I did the modification and still not working

1512337_pastedImage_3.png

This is the graphic result I have in my typology rule request window ;

1512341_pastedImage_4.png

But when i'm doing a data preview:

1512343_pastedImage_6.png

I don't get how to construct my subRequest without the SQLData !

Thanks by advance,

Regards,

View solution in original post

arouillard44
Level 4
Level 4

i'm still blocked by this request

Vipul Raghav​ any idea ?

pablo_rosero1
Level 10
Level 10

Hi,

Still need help?

Vipul Raghav​ can you please take a look?

Thanks!