Fred, Thanks for your response... I did consider the Direct SQL within the set field action. Problem with that would be during Submit.. when I fetch the data from PeopleSoft, there is no entry already committed to the interface table, during the submit operation so I cannot really run a direct SQL in a set field action to format the current transaction.. At least my thoughts were along those lines when I thought of that..
I am using Submit filters on triggering an AIE schedule to create entries in a interface table created for PeopleSoft. Here is where I am doing the data cleansing operation before using it to create or update any of the ITSM foundation data.. So since using SQL I would not be able to update a record that hasn't yet been committed to the database, I didn't even try to use it as I assumed it will fail.. Am I missing something? Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Behalf Of Grooms, Frederick W Sent: Thursday, April 03, 2008 5:57 PM To: [email protected] Subject: Re: Phone number formatting... ** Joe, You do know that you can use the TRANSLATE to drop characters as well (so you don't need to use REPLACE). If there is no matching character to translate to Oracle will drop that character off of the output completely. You can also use any Oracle function inside of a Set Fields SQL action. So to keep in line with the internal policies you can have a Filter on Submit and Modify do a Set Fields SQL action to clean up the fields. Fred ---------------------------------------------------------------------------- -- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Joe D'Souza Sent: Thursday, April 03, 2008 4:44 PM To: [email protected] Subject: Re: Phone number formatting... ** I didn't get what you were trying to send but, I did work out a pretty good query that cleaned up the results on a view form, but they are not willing to accept that solution as its a view that is build from the Oracle database, so outside the application interface.. they are looking for a solution built fully using the application interface.. Translate was the Oracle function I was looking for, in combination with another replace, to complete the rest to what I had posted i.e. to replace all free text alphabets with null... This is the query I wrote that formatted phone numbers as well as Address related fields.. Create or Replace view PSI_PSDC_VW_IBMCNA as Select REQUEST_ID, SUBMITTER, CREATE_DATE, ASSIGNED_TO, LAST_MODIFIED_BY, MODIFIED_DATE, STATUS, SHORT_DESCRIPTION, EMPL_ID, ALTER_EMPL_ID, NAME, FIRST_NAME, MIDDLE_NAME, LAST_NAME, PERSON_TYPE, PER_ORG, DESCR, CN_REPORTS_TO_ID, MANAGER_NAME, ACCT_CD, COST_CENTER_MANAGER, PAY_GROUP, JOB_CODE, JOB_CODE_DESCRIPTION, LOCATION, DEPT_ID, trim(replace(translate(replace(replace(replace(upper(replace(replace(repla ce(replace(replace(replace(replace(replace(replace(PHONE_HOME,'+',''), '/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),',','')),'.',' '),' ',' '),' ',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','^^^^^^^^^^^^^^^^^^^^^^^^^^'),'^','')) as PHONE_HOME, trim(replace(translate(replace(replace(replace(replace(replace(upper(repla ce(replace(replace(replace(replace(replace(replace(replace(replace(PHONE_BUS INESS,'+',''), '/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),',','')),'.',' '),' ',' '),' X','?'),' EXT','?'),' ',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','^^^^^^^^^^^^^^^^^^^^^^^^^^'),'^','')) as PHONE_BUSINESS, trim(replace(replace(replace(replace(translate(replace(replace(replace(upp er(replace(replace(replace(replace(replace(replace(replace(replace(replace(P HONE_PAGER,'+',''),'/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),' ,','')),'.',' '),' ',' '),' ',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','??????????????????????????'),'?',''),'@' ,''),':',''),'\','')) as PHONE_PAGER, trim(replace(replace(replace(replace(translate(replace(replace(replace(upp er(replace(replace(replace(replace(replace(replace(replace(replace(replace(P HONE_PAGER_2,'+',''),'/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*','') ,',','')),'.',' '),' ',' '),' ',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','??????????????????????????'),'?',''),'@' ,''),':',''),'\','')) as PHONE_PAGER_2, trim(replace(translate(replace(replace(replace(upper(replace(replace(repla ce(replace(replace(replace(replace(replace(replace(PHONE_CELL,'+',''), '/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),',','')),'.',' '),' ',' '),' ',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','??????????????????????????'),'?','')) as PHONE_CELL, trim(replace(translate(replace(replace(replace(replace(replace(upper(repla ce(replace(replace(replace(replace(replace(replace(replace(replace(PHONE_FAX ,'+',''), '/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),',','')),'.',' '),' ',' '),' OR ','/'),' ',''),'7ST',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','??????????????????????????'),' ?','')) as PHONE_FAX, trim(replace(translate(replace(replace(replace(replace(replace(replace(upp er(replace(replace(replace(replace(replace(replace(replace(replace(replace(P HONE_ALTERNATE_BUS,'+',''), '/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),',','')),'.',' '),' ',' '),' X','?'),' EXT','?'),' ',''),'PIN','?'),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','^^^^^^^^^^^^^^^^^^^^^^^^^^'), '^','')) as PHONE_ALTERNATE_BUS, EMPLOYEE_STATUS, GRADE, COMPANY, L2_DEPT_ID, L2_DESCR, MANAGER_POSITION, EMAIL_BUSINESS, POSITION_NUMBER, EMAIL_HOME, MANAGER_ID, REG_REGION, trim(replace(replace(replace(ADDRESS_1,'|',''),'~',''),' ',' ')) as ADDRESS_1, trim(replace(replace(replace(ADDRESS_2,'|',''),'~',''),' ',' ')) as ADDRESS_2, ADDRESS_3, ADDRESS_4, COUNTRY, STATE, trim(replace(replace(replace(CITY,'|',''),'~',''),' ',' ')) as CITY, trim(replace(replace(replace(POSTAL,'|',''),'~',''),' ',' ')) as POSTAL, SECTOR, trim(replace(replace(replace(ADDRESS_1_2,'|',''),'~',''),' ',' ')) as ADDRESS_1_2, trim(replace(replace(replace(ADDRESS_2_2,'|',''),'~',''),' ',' ')) as ADDRESS_2_2, ADDRESS_3_2, ADDRESS_4_PS, COUNTRY_2, STATE_2, CITY_2, POSTAL_2, HOURLY_RATE, LEVEL_NUMBER, PERSON_ID, AREA_BUSINESS, AREA_BUSINESS_FAX, AREA_HOME, AREA_MOBILE, AREA_PAGER, AREA_PAGER_NT, CC_BUSINESS, CC_BUSINESS_FAX, CC_HOME, CC_MOBILE, CC_PAGER, CC_PAGER_NT, EXTENSION_BUSINESS, LOCAL_BUSINESS, LOCAL_BUSINESS_FAX, LOCAL_HOME, LOCAL_MOBILE, LOCAL_PAGER, LOCAL_PAGER_NT, PIN_PAGER_NT, PIN_PAGER from PSI_PeopleSoftDataContainer_IB; I got phone numbers with extensions in the format 7777777777?7777 where ? represents that numbers following it are extensions. It works but unfortunately I cannot use it because of some of their internal policies there. Well at least I had some joy writing the above :-) Cheers Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Behalf Of PS_Is_Fun Sent: Thursday, April 03, 2008 4:55 PM To: [email protected] Subject: Re: Phone number formatting... Not sure if you got what you needed yet, but here's a straight sql query to do what you are asking: Joe D'Souza wrote: In the intention to clean up the phone number information that is coming from a PeopleSoft database that has phone numbers in a practically free format I am trying to write a select statement so that all non numeric characters in the field will be eliminated. A sample of the phone data could be ###777.777.7777 ext 7777 777/777-7777 Ext.7777 777-777.7777 X 7777/////// 777.777.7777 janes number!!!!! my number## 7777777777 x7777 +7777777777 ,this is my number Think of any permutation of text and number you will find it in this database as its virtually free format character field My aim with a select statement is to get it clean enough and remove all the bold part to make it look like 7777777777 x 7777 7777777777 x 7777 7777777777 x 7777 7777777777 x 7777 7777777777 x 7777 7777777777 x 7777 This is the SQL statement I wrote that got rid of most of the stuff BUT I am still wondering how I would able to exclude all alphabets with a single replace added to the already existing nest.. while converting every possible combination of ext Ext EXT etc to x.. So far I have written select length(replace((replace((replace((replace((replace((replace((replace((repl ace((replace(z_phone_altb,'+','')), '/','')),'-','')),'(','')),')','')),'!','')),'#','')),'*','')),',','')) as Width, replace((replace((replace((replace((replace((replace((replace((replace((repl ace(z_phone_altb,'+','')), '/','')),'-','')),'(','')),')','')),'!','')),'#','')),'*','')),',','') as Phone_altb from ps_z_****** order by Width; (I was interested in width to see if there may be some numbers in a possible incorrect format (less than 10 or greater than 10 for USA and so on) This is the typical output I got from the above statement.. 777.777.7777 ext 7777 7777777777 Ext.7777 777777.7777 X 7777 777.777.7777 janes number my number 7777777777 x7777 7777777777 this is my number I can remove the dots, no problem.. even did that, but then thought they might be useful till I took off all the Ext. or x. or ext. from the data and replaced that with x.. How do I get rid of all the other random alphabets while keeping the ext?? I thought of replacing ext with ?? but is there a single replace that I can write to get rid of everything in the range a to z and A to Z?? Any ideas? Joe D'Souza No virus found in this outgoing message. Checked by AVG. Version: 7.5.519 / Virus Database: 269.22.5/1358 - Release Date: 4/3/2008 6:36 PM _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
