Expand my Community achievements bar.

SOLVED

[AC Classic] Migration of SQLData

Avatar

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

Avatar

Correct answer by
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

8 Replies

Avatar

Level 10

Bonjour Alexandre,

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

Paramétrages généraux

Cdt

JS

Avatar

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

Avatar

Level 10

Alexandre,

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

Cdt

JS

Avatar

Level 10

<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

Level 4

Hello guys !

Thanks for your advices !

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

Regards,

Avatar

Correct answer by
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,

Avatar

Level 4

i'm still blocked by this request

Vipul Raghav​ any idea ?

Avatar

Level 10

Hi,

Still need help?

Vipul Raghav​ can you please take a look?

Thanks!