[AC Classic] Migration of SQLData | Community
Skip to main content
arouillard44
arouillard44
Level 4
June 18, 2018
Solved

[AC Classic] Migration of SQLData

  • June 18, 2018
  • 8 replies
  • 6095 views

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

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by arouillard44

    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,

    8 replies

    Jean-Serge_Biro
    Jean-Serge_Biro
    Level 10
    June 18, 2018

    Bonjour Alexandre,

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

    Paramétrages généraux

    Cdt

    JS

    arouillard44
    arouillard44Author
    Level 4
    June 18, 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

    Jean-Serge_Biro
    Jean-Serge_Biro
    Level 10
    June 19, 2018

    Alexandre,

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

    Cdt

    JS

      Amit_Kumar
      Amit_Kumar
      Level 10
      June 19, 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.

        arouillard44
        arouillard44Author
        Level 4
        June 20, 2018

        Hello guys !

        Thanks for your advices !

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

        Regards,

        arouillard44
        arouillard44AuthorAccepted solution
        Level 4
        June 20, 2018

        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
        arouillard44Author
        Level 4
        June 25, 2018

        i'm still blocked by this request

        Vipul Raghav​ any idea ?

        pablo_rosero1
        pablo_rosero1
        Level 9
        November 29, 2018

        Hi,

        Still need help?

        Vipul Raghav​ can you please take a look?

        Thanks!

        Terms & ConditionsAccessibility statement

        Loading footer...