OK - I'm a little confused, which is unfortunately not very unusual these days. :)
Fact: I tried the command as is and it did not work. Fact: I added the ampersand and it still did not work. Fact: I did a SHOW VAR and found the variable was missing the "e". Fact: I reduced the variable name to 14 characters. Fact: It worked! Question: WHY? Steve Sweeney -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Steve Breen Sent: Friday, April 29, 2005 5:11 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: [No Subject] Steve, You were not eve close. Read this from Bill Steve, Sorry. Maybe this syntax will work: ALTER TABLE CHARGES DROP CONSTRAINT &vConstraintName Bill -----Original Message----- From: Steve Sweeney [mailto:[EMAIL PROTECTED] Sent: Friday, April 29, 2005 6:58 PM To: [email protected] Subject: [RBG7-L] - Re: [No Subject] The problem is in the length of the variable name. The variable "vConstraintName" is 15 characters. Reduce it by one character (remove the letter "e" - vConstraintNam) and it will work. Steve -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Steve Breen Sent: Friday, April 29, 2005 4:33 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: [No Subject] Bill, The following is the reason I am confused. I have tried all this and still keep getting the same results. Originally I pasted your code into my file and ran in and got the following: -ERROR- Invalid constraint identifier. (2743) The current code is: SELECT sys_index_name INTO vConstraintName + FROM sys_indexes + WHERE sys_table_id = + (sel sys_table_id from sys_tables where sys_table_name = 'Charges') + AND sys_column_name = 'chargetype' + and sys_foreign_key <> 0 ALTER TABLE CHARGES DROP CONSTRAINT .vConstraintName Still Get: -ERROR- Invalid constraint identifier. (2743) The folling are some R prompt results: R>sel sys_index_name into vConstraintName from sys_indexes where sys_table_id+ +> = (sel sys_table_id from sys_tables where sys_table_name = 'Charges') and + +>sys_column_name = 'chargetype' and sys_foreign_key <> 0 R>show var .vconstraintname -ERROR- Variable #60 is not defined. ( 20) R>run archive8.rmd -ERROR- deltable is an undefined table. (2038) R>show var .vconstraintname -ERROR- Variable #60 is not defined. ( 20) R>run archive8.rmd -ERROR- deltable is an undefined table. (2038) -ERROR- Invalid constraint identifier. (2743) R>show var vconstraintname #60 R>ALTER TABLE CHARGES DROP CONSTRAINT vConstraintName -ERROR- Invalid constraint identifier. (2743) R>ALTER TABLE CHARGES DROP CONSTRAINT .vConstraintName -ERROR- Invalid constraint identifier. (2743) R> -----Original Message----- From: Bill Downall [mailto:[EMAIL PROTECTED] Sent: Friday, April 29, 2005 6:16 PM To: [email protected] Subject: [RBG7-L] - Re: [No Subject] Steve, the syntax of SHOW VAR "expects" a variable after the word var. You don't need to "dot" it. In fact, when you "dot" it, R:Base thinks you want the variable NAMED whatever the current value in the variable is. You typed: SHOW VAR .vconstraintname If you: SHOW VAR vconstraintname You will see '#60' is the name of the constraint. Then all you have to do is: ALTER TABLE Charges DROP CONSTRAINT #60 (or, in a program:) ALTER TABLE Charges DROP CONSTRAINT .vConstraintname Bill Steve Breen wrote: > > > Bill, > > I finally worked it down to this. > > What do you think. > > R>sel sys_index_name into vConstraintName from sys_indexes where > sys_table_id + > = (sel sys_table_id from sys_tables where sys_table_name = 'Charges') and + > sys_column_name = 'chargetype' and sys_foreign_key <> 0 > R>show var .vconstraintname > -ERROR- Variable #60 is not defined. ( 20) > > > > Stephen Breen > > CDI Services, Inc. > > <http://www.911roadrepair.com> > > >
