I was just trying to understand why you would need an extra view or SQL files? Fred
________________________________ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Joe D'Souza Sent: Saturday, March 29, 2008 6:48 PM To: arslist@ARSLIST.ORG Subject: Re: Equivalent of the TRANSLATE function in Remedy ** Yes the thing Is I don't think I am allowed to use direct SQL.. I have a statement ready for use like you suggested, plus I have a view ready with clean data, but have not got approvals to use that. The thing is this customer does not have an onsite Remedy admin and are unwilling at the moment to use external means to drive their applications.. They are worried about maintaining it after I leave even if I do leave them with SQL scripts and instructions how to maintain the view using those .SQL files.. I 'might' just have to resort to a Filter guide having those nasty 26 nested replaces.. not sure what that would cost the transaction overall, but I don't see many other ways out of this.. Mathew's java scripting plugin idea may be good too, but that again is creating an external plugin which I do not think they would support. Besides I don't think we have time enough on hands left for that. Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Behalf Of Grooms, Frederick W Sent: Saturday, March 29, 2008 4:16 PM To: arslist@ARSLIST.ORG Subject: Re: Equivalent of the TRANSLATE function in Remedy ** You are correct ... that is extremely nasty If you were allowed to use an Oracle (asuming you are on oracle) SQL Set Fields action you could do it like SELECT TRANSLATE( REPLACE( TRANSLATE( UPPER('$PHONE_BUSINESS$'), '+/-()!#*,.', ''), 'EXT', '?'), 'XABCDEFGHIGKLMNOPQRSTUVWYZ', '?') FROM dual No need for any type of view and all of it is contained inside Remedy. Fred ________________________________ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Joe D'Souza Sent: Friday, March 28, 2008 6:33 PM To: arslist@ARSLIST.ORG Subject: Re: Equivalent of the TRANSLATE function in Remedy ** Rick, Not sure if you can use the ASCII range, never tried it.. Thad, Yes same phone number issue and while doing it directly at SQL yes I did use Upper before Translate so I have to translate less... The actual select statement that I designed, as a result of all the formatting that was necessary, was too long and may not be fun for some of you to read on a day when Friday Humor is posted, so I'll copy how I read one of the phone numbers while creating a db view instead of the whole SQL statement I wrote.. Unfortunately the customer wants nothing to do with external db views or direct SQL to the view, so I cannot use it but need to translate the SQL below to ARS code.. While I am good with translating TRIM to LTRIM and RTRIM and REPLACE and UPPER to REPLACE and UPPER in Remedy, my only option seems to be a nested replace 26 times for alpha characters instead of a single TRANSLATE.. trim(replace(translate(replace(replace(replace(replace(replace(upper(rep lace(replace(replace(replace(replace(replace(replace(replace(replace(PHO NE_BUSINESS,'+',''), '/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),',','')),'.',' '),' ',' '),' X','?'),' EXT','?'),' ',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','^^^^^^^^^^^^^^^^^^^^^^^^^^'),'^','') ) as PHONE_BUSINESS, The above reads the Phone_Business column in the external view after stripping it off all the junk, and replacing " EXT" or " X" for extension with a "?".. That delimit of "?" would tell me if a phone number has an extension and where the extension string began from.. This replacement is done before the translation of alpha characters to ^ so we do not loose the extension information - I have taken care of that! So the end result is a numeric character string. I created a view with the above statement applied to most phone number columns during the view creation and got a clean view without loosing extension information in any of the records.. (32101 records) So far I was happy except that now it looks like to translate the above statement to Remedy code might be a more expensive process considering that Remedy doesn't have a TRANSLATE function.. I wonder if cases like this would qualify for an RFE.. TRANSLATE is used by all RDBMS's right??? I know it is used in MS-SQL too.. not so sure about My-SQL and Informix.. Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Behalf Of Thad K Esser Sent: Friday, March 28, 2008 6:53 PM To: arslist@ARSLIST.ORG Subject: Re: Equivalent of the TRANSLATE function in Remedy ** Joe, Sorry, I'm not sure there's an easy answer to this one without using the SQL. I think you'll have to loop through the string one character at a time to doing your find and replace. Some thoughts though: 1. Does this have to do with your phone number formatting issue? Would it be easier to approach the problem by looking to include valid values (numbers) versus replacing non-valid values (alpha)? 2. If you have to create a nested replace for all alphabetic characters, since you're going to remove them anyway, use an UPPER() or LOWER() on the string first, so you only have to do half as many replacements. 3. Is the server on Unix? Could you use a unix Translate command ( http://en.wikipedia.org/wiki/Tr_(Unix) ) in a run process instead of SQL? (Oh hell, I just looked it up, of course the command is "tr". I can't believe I have to suggest that. :-) Anyway, I hope that helps some. Thad Esser Remedy Developer "Argue for your limitations, and sure enough, they're yours."-- Richard Bach "Joe D'Souza" <[EMAIL PROTECTED]> Sent by: "Action Request System discussion list(ARSList)" <arslist@ARSLIST.ORG> 03/28/2008 03:00 PM Please respond to arslist@ARSLIST.ORG To arslist@ARSLIST.ORG cc Subject Equivalent of the TRANSLATE function in Remedy ** Has anyone implemented the equivalent of the Oracle TRANSLATE function in Remedy? I have a field where I wish to translate all the alpha characters into lets say ^ and then replace ^ with null to make that field free of alpha characters. The only way I can think it is possible right now is to have a nested REPLACE for each character. Any other method? I have been asked to avoid direct SQL's as far as I can so I'm going to resort to Direct SQL as a last resort option.. Thanks Thad for responding to that one.. Maybe you know the answer to this one too :-). Unfortunately on this gig I do not have that much of a luxury to 'try' it out' Cheers Joe D'Souza __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
