Delphix Products

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



#Masking

Hi Pankay,

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

Best Regards.
Gianpiero

Thanks a lot for your replay ,Could you please give me the steps how we can do it using delphix ...
We have tried but may be doing something wrong m it will be great help if you share steps 

https://docs.delphix.com/pages/viewpage.action?pageId=29165230#ModifyingTablesinaRuleSet(ForDistribu...">https://docs.delphix.com/pages/viewpage.action?pageId=29165230#ModifyingTablesinaRuleSet(ForDistributedEnvironment))-UsingCustomSQL">https://docs.delphix.com/pages/viewpage.action?pageId=29165230#ModifyingTablesinaRuleSet(ForDistribu...

Could you please send me your custom SQL ? I'll take a look into it in order to help you.
Thank you.
Gianpiero

i want to make the column name in uppercase  hence i  have written in custom sql 

select upper(col_name) from table name 

Getting error after running the masking job 

Do not rewrite the custom SQL. Please, push the delete button and then paste here the custom sql. I'll try to modify it without rewrite it entirely.
Thanks.
Gianpiero

Could you please suggest me what exactly i have to write ?
to make the column name in uppercase or to perfrom operation on any specific column please let us know we are blocked 

Without original custom sql, it's difficult for me. I suppose you're using Oracle.
Let's try:
Try to delete your custom sql clicking the delete button.
Go back to the custom sql tab to retrieve the original sql.
Wrap only the column "firstname" with upper function and give it back the original name with alias "AS":
select rowid, "C1","C2",UPPER("C3") AS "C3" FROM ....

Steps :
1. create env and application
2. Create connection
3. Create profiler and Rule set 
4. Run profiler and assign the requird algorithm
5. Go to Rule Edit > Set add filter > Give any name > save it 
6. Click on Edit pencil icon on rule set screen
7. Click on custom SQL radio button and add below query in this 
 SELECT Upper(BO_NAME_DISP_UPPER) AS test, PREF_FIRST_NAME, FROM TEST_Table

8. Run masking Job for that table
Job is getting failed and nothing is happen  
Now please tell me the solution 

This is the error m getting :
Mar 07, 2018 12:03:23 PM com.dmsuite.common.utils.JobExecutionHelper executeJob
INFO: Job started at Wed Mar 07 12:03:23 UTC 2018.
Mar 07, 2018 12:03:23 PM com.dmsuite.dmsApplicator.masking.XMLGenerator executeMarshalling
INFO: Generate request xml started successfully.
Mar 07, 2018 12:03:23 PM com.dmsuite.dmsApplicator.masking.XMLGenerator executeMarshalling
INFO: Generate Request xml done successfully.
Mar 07, 2018 12:03:23 PM com.dmsuite.dmsApplicator.masking.transformation.MaskingMarshalling createKettleXML
INFO: Generate Transformation XML started successfully
Mar 07, 2018 12:03:24 PM com.dmsuite.dmsApplicator.masking.transformation.MaskingMarshalling createKettleXML
INFO: Generate Transformation XML done successfully
Mar 07, 2018 12:03:24 PM com.dmsuite.dmsApplicator.job.transformation.JobXMLGenerator createMaskingKettleJobXML
INFO: Generate job xml started successfully.
Mar 07, 2018 12:03:24 PM com.dmsuite.dmsApplicator.job.transformation.JobXMLGenerator createMaskingKettleJobXML
INFO: Generate job xml done successfully.
Mar 07, 2018 12:03:29 PM com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl createMaskingResponseXML
INFO: Job Status : FAIL. 
Mar 07, 2018 12:03:29 PM com.dmsuite.dmsApplicator.masking.MaskResponseXMLUnmarshaller saveExecution
INFO: save execution started successfully.
Mar 07, 2018 12:03:29 PM com.dmsuite.dmsApplicator.masking.MaskResponseXMLUnmarshaller saveExecution
INFO: save execution done successfully.
Mar 07, 2018 12:03:29 PM com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl run
INFO: Job Ended at Wed Mar 07 12:03:29 UTC 2018.

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.

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 

Could you please give me any suggestion please 

Could you please help me on this 

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

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