[AC Classic] Migration of SQLData

Avatar

Avatar

arouillard44

Avatar

arouillard44

arouillard44

18-06-2018

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

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar

arouillard44

Avatar

arouillard44

arouillard44

20-06-2018

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,

Answers (7)

Answers (7)

Avatar

Avatar

Amit_Kumar

MVP

Avatar

Amit_Kumar

MVP

Amit_Kumar
MVP

19-06-2018

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

Avatar

Avatar

Jean-Serge_Biro

MVP

Avatar

Jean-Serge_Biro

MVP

Jean-Serge_Biro
MVP

19-06-2018

Alexandre,

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

Cdt

JS

Avatar

Avatar

pablo_rosero1

Avatar

pablo_rosero1

pablo_rosero1

28-11-2018

Hi,

Still need help?

Vipul Raghav​ can you please take a look?

Thanks!

Avatar

Avatar

arouillard44

Avatar

arouillard44

arouillard44

25-06-2018

i'm still blocked by this request

Vipul Raghav​ any idea ?

Avatar

Avatar

arouillard44

Avatar

arouillard44

arouillard44

20-06-2018

Hello guys !

Thanks for your advices !

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

Regards,

Avatar

Avatar

arouillard44

Avatar

arouillard44

arouillard44

18-06-2018

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

Avatar

Avatar

Jean-Serge_Biro

MVP

Avatar

Jean-Serge_Biro

MVP

Jean-Serge_Biro
MVP

18-06-2018

Bonjour Alexandre,

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

Paramétrages généraux

Cdt

JS