Custom/Sequential Ids

Avatar

Avatar

eriku40433679

Avatar

eriku40433679

eriku40433679

05-06-2019

Hi all,

We have a system where we populate the account number field with the CRM id. We then use the account number as a foreign key to other schemas in campaign. My problem begins in scenarios where when we load entries that will never be loaded in CRM, so I must make up some unique account number for them, prefixed with some unique identifier like temp001020 or something. Account Number remains a varchar field.

I've tried to setup a sequence in the database and added a calculated field in the enrichment with 'temp'+NEXTVAL('mySequenceName') but I'm getting a "You're not authorized to use SQL expressions" error, even though I have the named right.

How can I go about setting this up?

Thank you,

--Erik

View Entire Topic

Avatar

Avatar

pierrec70731455

Avatar

pierrec70731455

pierrec70731455

17-06-2019

Hi Erik,

you could create mutilple sequences in SQL scripts like this :

SQL script sequence.png

And by defining an SQL function to get the next value of your sequences (Adding additional SQL functions😞

<?xml version="1.0" encoding='ISO-8859-1' ?>

<!-- ===========================================================================

  Additional SQL functions for Adobe Campaign

  ========================================================================== -->

<package

  namespace   = "nms"

  name        = "package-funclist-nextSeqVal"

  label       = "nextSeqVal function"

  buildVersion= "6.1"

  buildNumber = "8721">

  <entities schema="xtk:funcList">

    <funcList name="sequenceFunctions" namespace="crf">

      <group name="numeric" label="Numerical">

        <function name="nextSeqVal" type="long" args="(&lt;SequenceName&gt;)" help="get next value of specified sequence ex.: nextSeqVal('negativePersonID')"

                  minArgs="1" maxArgs="1" display="Get next value of sequence '$1'">

          <providerPart provider="PostgreSQL,MSSQL" body="nextval($1)"/>

        </function>

      </group>

    </funcList>

  </entities>

</package>

You can then use the sequence of your choice :

Update Data with Sequence.png

Kind regards,

Pierre