Referential Integrity between two differently formatted name fields

  • 0
  • 1
  • Question
  • Updated 8 months ago
  • Answered
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? 
Photo of Renee Greenley

Renee Greenley

  • 604 Points 500 badge 2x thumb

Posted 8 months ago

  • 0
  • 1
Photo of Hims

Hims, Employee

  • 2,126 Points 2k badge 2x thumb
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
Photo of Renee Greenley

Renee Greenley

  • 604 Points 500 badge 2x thumb
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
Photo of Mouhssine SAIDI

Mouhssine SAIDI

  • 4,732 Points 4k badge 2x thumb
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
Photo of Renee Greenley

Renee Greenley

  • 604 Points 500 badge 2x thumb
Mouhssine,

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

Thanks,
Renee
Photo of Jatinder Luthra

Jatinder Luthra, Employee

  • 2,946 Points 2k badge 2x thumb
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
Photo of Renee Greenley

Renee Greenley

  • 604 Points 500 badge 2x thumb
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