Delphix Products

Expand all | Collapse all

Referential Integrity between two differently formatted name fields

Jump to Best Answer
  • 1.  Referential Integrity between two differently formatted name fields

    Posted 01-12-2018 02:30:00 PM
    I see that referential integrity is maintained for masked names when using the default 'first name sl' and 'last name sl' algorithms. However, I have other tables with the same people that have the name formatted as one field with the Last name, First name in one field. If I use the default 'Last comma First SL' algorithm it does not maintain referential integrity with the tables that break those names into two separate fields. That does not surprise me, but wondering if there is anyway to achieve referential integrity with the names between tables that use those two different formats? 

    #Masking


  • 2.  RE: Referential Integrity between two differently formatted name fields
    Best Answer

    Posted 01-12-2018 02:51:00 PM
    Hi Renee,
    If I may rephrase the question, you want to achieve the following:
    John             -> James  Smith            -> Bond  John Smith       -> James Bond  Smith, John      -> Bond, James
    Yes, this is commonly done, however not with shipped algorithms. you can use :
    1. Post-Script SQL to achieve this, we will attach a SQL to the masking job which will concatenate the full name field after masking.
    2. Use a Delphix custom algorithm. Please reach out to Delphix Rep for this.
    Please let me know.

    --Hims


  • 3.  RE: Referential Integrity between two differently formatted name fields

    Posted 01-12-2018 03:02:00 PM
    Thank you Hims. Yes this is exactly what I need to do. How should I proceed to get the SQL or custom algorithm.

    Thanks!
    Renee


  • 4.  RE: Referential Integrity between two differently formatted name fields

    Posted 01-12-2018 03:58:00 PM
    Hi Renee,

    Another idea is using as post-script a trigger that concatenates the filed this way you do all in one pass.

    Regards,

    Mouhssine


  • 5.  RE: Referential Integrity between two differently formatted name fields

    Posted 01-15-2018 08:30:00 PM
    Mouhssine,

    Could you give me an example of what a post-script might look like for this scenario?

    Thanks,
    Renee


  • 6.  RE: Referential Integrity between two differently formatted name fields

    Posted 01-15-2018 09:19:00 PM
    Hello Renne,
                      As per my understanding from previous conversation, post-script can actually take simple update statement to update FULL NAME column. If it involves more than one table, you can do joins and built sql statement, which can be used as post script.

    Something as below:

    Update <table> set FULL_NAME = LAST_NAME ||','|| FIRST_NAME;

    Hope this helps.

    Thanks


  • 7.  RE: Referential Integrity between two differently formatted name fields
    Best Answer

    Posted 01-16-2018 12:09:00 PM
    Thank you Jatinder. I now have a better idea of what to do with a potential post script. I will give that a try.

    Renee