Getting wrong value for the same name using secure lookup algorithm

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • Answered
Hello,

We have two columns in DB
first name and display namem first name is in small case and display name is in Upper case when we applying the same algorithm on both column different values are getting populated how we can generate the same name for both fields?


Example;
firstname= marc venu
Displayname is = MARC VENU

apply same algorithm domain is first name and algorithm is FULL NAME SL
run masking job 
Check first name and display name after masking job completion
first name= getu hans
Display name = marcin jhon

Expected that we need is:

first name= getu hans
Display name = GETU HANS

Please let me know the solution
Photo of pankaj

pankaj

  • 492 Points 250 badge 2x thumb

Posted 6 months ago

  • 0
  • 1
Photo of Gianpiero Piccolo

Gianpiero Piccolo

  • 1,466 Points 1k badge 2x thumb
Hi Pankay,

you can use a custom SQL in your Ruleset in order to use UPPER sql function to your column.

Best Regards.
Gianpiero
Photo of Gianpiero Piccolo

Gianpiero Piccolo

  • 1,466 Points 1k badge 2x thumb
Hi Pankaj,

the query seems to be wrong:
SELECT Upper(BO_NAME_DISP_UPPER) AS test, PREF_FIRST_NAME, FROM TEST_Table

there is a comma between the last column name and FROM keywork.
You changed the name of the originl column with the alias AS test, you should have called AS BO_NAME_DISP_UPPER

Please, leave the original query unchanged (delete the current modified by you) and paste here.
Photo of pankaj

pankaj

  • 492 Points 250 badge 2x thumb
SELECT ROWID, "BO_NAME_DISP_UPPER" , "PREF_FIRST_NAME" , "NAME_TYPE" , "BO_ID" , "BO_NAME_SHORT" , "LAST_NAME" , "FIRST_NAME" , "NAME_ROYAL_SUFFIX" , "BGC_PHONETIC" , "SYNCDTTM" , "PRIMARY_IND" , "NAME_INITIALS" , "ROW_LASTMANT_DTTM" , "NAME_ROYAL_PREFIX" , "MIDDLE_NAME" , "SYNCID" , "SEQ_NBR" , "ROW_LASTMANT_OPRID" , "ROW_ADDED_DTTM" , "NAME_TITLE" , "SOURCE_IND" , "SECOND_LAST_SRCH" , "LAST_NAME_SRCH" , "ROW_ADDED_OPRID" , "SECOND_LAST_NAME" , "BGC_PHONETIC_SRCH" , "COUNTRY_NM_FORMAT" , "BO_NAME_AC" , "NAME" , "NAME_PREFIX" , "FIRST_NAME_SRCH" , "NAME_AC" , "BO_NAME_DISPLAY" , "NAME_SUFFIX" , "BO_NAME" ,upper( BO_NAME_UPPER) as "BO_NAME_UPPER" FROM "CII_SCHE"."TEST_BCI"





Job is getting succeeded but when queering the table not able to see the  BO_NAME_UPPER in upper case

Aslo we have Firstname SL masking algorithm for this column how to do it 
Photo of pankaj

pankaj

  • 492 Points 250 badge 2x thumb
Could you please give me any suggestion please 
Photo of pankaj

pankaj

  • 492 Points 250 badge 2x thumb
Could you please help me on this 
Photo of Gianpiero Piccolo

Gianpiero Piccolo

  • 1,466 Points 1k badge 2x thumb
There are two problems:

***INPUT VALUE PROBLEM***
how to assure that two input values that differ only for upper/lower case generate the same output value? "Mario" -> "Sergio" and "MARIO" -> "Sergio" ?

Below the solution:

All case different columns are to be upperized. Below I suppose that FIRST_NAME can contain lower case characters and so we are going to upperize it:

SELECT ROWID, "BO_NAME_DISP_UPPER" , "PREF_FIRST_NAME" , "NAME_TYPE" , "BO_ID" , "BO_NAME_SHORT" , "LAST_NAME" , UPPER("FIRST_NAME") AS "FIRST_NAME", "NAME_ROYAL_SUFFIX" , "BGC_PHONETIC" , "SYNCDTTM" , "PRIMARY_IND" , "NAME_INITIALS" , "ROW_LASTMANT_DTTM" , "NAME_ROYAL_PREFIX" , "MIDDLE_NAME" , "SYNCID" , "SEQ_NBR" , "ROW_LASTMANT_OPRID" , "ROW_ADDED_DTTM" , "NAME_TITLE" , "SOURCE_IND" , "SECOND_LAST_SRCH" , "LAST_NAME_SRCH" , "ROW_ADDED_OPRID" , "SECOND_LAST_NAME" , "BGC_PHONETIC_SRCH" , "COUNTRY_NM_FORMAT" , "BO_NAME_AC" , "NAME" , "NAME_PREFIX" , "FIRST_NAME_SRCH" , "NAME_AC" , "BO_NAME_DISPLAY" , "NAME_SUFFIX" , "BO_NAME" ,upper( BO_NAME_UPPER) as "BO_NAME_UPPER" FROM "CII_SCHE"."TEST_BCI"

***OUTPUT VALUE PROBLEM***

How to get output value always in uppercase form?
You have to create a SL Algorithm uploading a txt file with all charcters in upper case.
If you need in some cases upper case output and other cases lower case output, you can simply build two SL Algorithms with the same row order and values but one file with lower case chars and the other one with upper case chars.

----------

I hope that I undestood your problems. If I got them, this surely can solve them.

Best Regards.
Gianpiero
Photo of Mouhssine SAIDI

Mouhssine SAIDI

  • 4,732 Points 4k badge 2x thumb
Hi Pankaj,

If I got your case right you have first name column with data in it and want to get the same masked value on this column to be propagated to displayname but in upper case.

One way for achieving that is relying on post-hooks on masking job;

Use algorithm to mask first name (all lower case) and call a post-script that updates displayname column with the masked value of firstname (using upper clause) to get values in upper case

Hope this answer your need.

Regards,

Mouhssine