Scott, ARS v6.3
Don't Application-Map-Ids-To-Names and Application-Map-Ids-To-Names-L return the Remedy database name of the field instead of the SQL Server View name for the field? I am using this approach to convert a Remedy qualification (used in the EXTERNAL() function) to Crystal Reports format qualification. *See the "Crystal Reports - Convert Remedy Qualification to RecordSelectionFormula" thread from March for details. For the most part it's finished. The only thing left to program is handling embedded double-quotes and the NOT operator. The reports that come with Help Desk 5.5 use SQL Views from Remedy (ie. HPD_HelpDesk, CHG_Change, CHG_Task), so I need to determine the names of the fields from the SQL View. For example, the SQL View field name for "Change ID+" is "Change_ID_". The SQL function below should handle every field used for our reporting, yet the function cannot guarantee 100% accuracy. Of course if there ever comes a time where a specific field is not converted to the correct SQL View field name then I can simply add a check to the function to handle that field. However, I prefer to use something that is guaranteed to be 100% accurate. I was hoping to find a way to query SQL Server (1st choice as this could be used with non-Remedy apps) or query ARS (2nd choice) to get the exact spelling of a SQL View field. Stephen -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Scott Parrish Sent: Thursday, May 04, 2006 11:37 AM To: arslist@ARSLIST.ORG Subject: Re: Get Base Column Name From SQL View Definition - SQL Server 2000 Stephen, I'm not sure what version of ARS you are using, but was just curious why you do not use either of these Run Process commands: Application-Map-Ids-To-Names <form> <string> Maps the IDs of the fields or keywords in the string to a name representation. Application-Map-Ids-To-Names-L <form> <VUI> <string> For the indicated VUI, maps the IDs of the fields or keywords in the string to a name representation using labels. If the field label is blank, the database name will be used. Scott Parrish IT Prophets, LLC (770) 653-5203 http://www.itprophets.com -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Heider, Stephen Sent: Thursday, May 04, 2006 10:17 AM To: arslist@ARSLIST.ORG Subject: Re: Get Base Column Name From SQL View Definition - SQL Server 2000 Update: Replace IF ISNUMERIC(@Char) = 1 OR (LOWER(@Char) BETWEEN 'a' AND 'z') With IF @Char BETWEEN '0' AND '9' OR (LOWER(@Char) BETWEEN 'a' AND 'z') The ISNUMERIC function consideres the plus sign numeric. -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Heider, Stephen Sent: Thursday, May 04, 2006 9:58 AM To: arslist@ARSLIST.ORG Subject: Re: Get Base Column Name From SQL View Definition - SQL Server 2000 Rick, Actually the ultimate goal is to retrieve the SQL View name of the field (ie. My_Special_Field) based on the table column name (ie. C123456789). This is the reverse of the example I presented below. *I thought this example would be easier for others to grasp the concept of parsing the base column names - I apologize if I confused anyone. Here is perhaps a better example: I know that the table column name is C123456789. How can I retrieve the field name used by the SQL View? I just created a work-around that should work fine - at least for my purposes. Here is a function that returns the SQL View name or SQL View Field name from a Remedy Form name or Remedy Field name. If anyone knows a better way, please share. Thanks. CREATE FUNCTION dbo.fnRemedyFormOrFieldNameToSQLViewName ( @FormOrFieldName VARCHAR(100) ) RETURNS VARCHAR(30) /* Returns the SQL View name of a Remedy form or the SQL View Column name of a Remedy field. Note: Trailing spaces in @FormOrFieldName are ignored. Internal ARS rules regarding naming Views (DatabaseRefGuide-630.pdf, page 41) All alphabetic and numeric characters remain as defined. All other characters are converted to an underscore (_). If the first character is not alphanumeric, a leading A is added to the name. If the name of a field is blank, a field name with a leading A followed by the fieldId is used. *Not used by this function If the name is one of the reserved words for the database, the string _x is appended. *Not used by this function */ AS BEGIN DECLARE @ReturnValue VARCHAR(30) DECLARE @Pos INT DECLARE @EndPos INT DECLARE @Char CHAR(1) IF LTRIM(RTRIM(ISNULL(@FormOrFieldName,''))) = '' RETURN '' SET @Pos = 1 SET @EndPos = LEN(@FormOrFieldName) IF @EndPos > 30 SET @EndPos = 30 -- ARS limit. SET @ReturnValue = '' WHILE @Pos <= @EndPos BEGIN SET @Char = SUBSTRING(@FormOrFieldName, @Pos, 1) IF ISNUMERIC(@Char) = 1 OR (LOWER(@Char) BETWEEN 'a' AND 'z') SET @ReturnValue = @ReturnValue + @Char ELSE IF @Pos = 1 SET @ReturnValue = 'A' ELSE SET @ReturnValue = @ReturnValue + '_' SET @Pos = @Pos + 1 END RETURN @ReturnValue END -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Rick Cook Sent: Thursday, May 04, 2006 9:28 AM To: arslist@ARSLIST.ORG Subject: Re: Get Base Column Name From SQL View Definition - SQL Server 2000 Stephen, if you're trying to return the column name, know that it's the DB name of the Remedy field. If you're trying to get the column ID, that's in the metatables. I built (as have many others) a simple ARS form to return the column names and field IDs from a given form, using either the T# or the Form name. If you would find it helpful, let me know, and I'll send it to you offline. If nothing else, you should be able to pull the SQL from the menus for non-ARS use. Rick -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Heider, Stephen Sent: Thursday, May 04, 2006 5:21 AM To: arslist@ARSLIST.ORG Subject: OT: Get Base Column Name From SQL View Definition - SQL Server 2000 The solution will be used with Remedy but it is a general SQL Server question. How can you get the base column name in a SQL View definition from the view field name? For example, CREATE VIEW uvwTest (FieldName1, FieldName2, FieldName3, FieldName4) AS SELECT BaseColumn1, BaseColumn2, CONVERT(VARCHAR(30), BaseColumn3) AS BaseColumn3, BaseColumn4 FROM BaseTable Let's say I need to get the base column name for the FieldName4 view field. My first approach was to determine the position of the field (in this case FieldName4 happens to be the 4th field) and then grab the corresponding base column using the commas as delimiters. However this does not work if there are extra commas [as used in calculated fields]. In this example the CONVERT function adds a comma to list of base columns. I have looked at the SQL Server sysobjects (and related tables) and the INFORMATION_SCHEMA objects, but no luck. I know that SQL Server itself knows how to parse the list in at least two places: When it executes the View and when you use the Design View wizard in SQL Server Enterprise Manager. Any ideas? Thanks. Stephen ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org ________________________________________________________________________ ____ ___ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
