[AC Classic] Migration of SQLData

[arouillard44]

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

[Jean-Serge_Biro]

Bonjour Alexandre,

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

Paramétrages généraux

Cdt

JS

[arouillard44]

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]

Alexandre,

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

Cdt

JS

[Amit_Kumar]

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

Hello guys !

Thanks for your advices !

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

Regards,

[arouillard44 · Accepted solution]

Hello guys,

I did the modification and still not working

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

But when i'm doing a data preview:

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

Thanks by advance,

Regards,

[arouillard44]

i'm still blocked by this request

Vipul Raghav​ any idea ?

[pablo_rosero1]

Hi,

Still need help?

Vipul Raghav​ can you please take a look?

Thanks!