Fixing Duplicate Saleslogix FieldIndex Values in the SECTABLEDEFS

I had to fix a client’s SECTABLEDEFS table that contained duplicate FieldIndex values.  FieldIndex is used by the Saleslogix field level security functionality.  Each FieldIndex should be a unique incremental number.  Here is the SQL I came up with.  This worked for me, but with any SQL statements, run only if you know what you are doing, and even then, do it in a test copy of the database to ensure no unexpected outcomes are encountered!

 

update SECTABLEDEFS set

FIELDINDEX = z.newindex

from

(select a.fieldname, a.fieldindex, t.fieldindex+x.topindex newindex from

sectabledefs a inner join

   (

   select ROW_NUMBER() over(order by tablename,a.fieldname) fieldindex, tablename,a.fieldname

    from SECTABLEDEFS a inner join   

    (select min(fieldname) fieldname, fieldindex

   from

            sysdba.sectabledefs

      group by

            fieldindex 

      having

            count(fieldindex)>1) x on a.FIELDINDEX=x.FIELDINDEX and a.FIELDNAME=x.fieldname             

    group by tablename,a.fieldname

 

   ) t on a.FIELDNAME=t.FIELDNAME

    and

   a.TABLENAME=t.TABLENAME

   ,

      (select MAX(fieldindex) topindex from SECTABLEDEFS) x

 

–order by t.fieldindex+x.topindex ,a.FIELDINDEX

) z

where SECTABLEDEFS.FIELDINDEX=z.FIELDINDEX and SECTABLEDEFS.FIELDNAME=z.FIELDNAME

 

ABOUT THE AUTHOR

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!