Fred! You're the man! I had almost forgotten about the option to use the pseudo table dual! When I realized the entry is not yet committed to the database I thought it was pointless to even consider direct SQL! All I saw was a dead end :-) But now I know better - and thank you!!!!
I haven't tried it as yet but I'm pretty sure using dual to process my direct SQL transaction should pretty much work! I had not read your previous email completely before answering to it.. Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Behalf Of Grooms, Frederick W Sent: Thursday, April 03, 2008 6:20 PM To: arslist@ARSLIST.ORG Subject: Re: Phone number formatting... ** I just realized I meant to give an example Say you are on an Oracle db and you want to force all of your Login Names in the User form to be created as upper case only. You could do a Filter on Submit, Modify, Merge of Form User with a Set Fields action. Set the field "Read Value for Field From" to SQL The SQL command would be: Select TRANSLATE( '$Login Name$', 'abcdefghijklmnopqrstuvwxyz', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ) From DUAL Set Field Login Name = SQL Result Column $1$ I know it is kind of a silly example, but I think it shows the versatility of the Set Fields from SQL action and the Oracle psuedotable DUAL Fred ---------------------------------------------------------------------------- -- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: Thursday, April 03, 2008 4:57 PM To: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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 _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
