I have been successful in creating the temp tables, having the information append to the temp tables, editing the information but am stuck on the next part. Since I have a PK and a FK set up on the regular tables (QuoteHeader and QuoteDetail) the system will not allow me to delete the rows from the tables one at a time. Is there a work around on tables with PK & FK?
Jim ________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Alastair Burr Sent: Monday, November 17, 2008 12:43 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Inserting into temporary table Jim, Without knowing exactly what you're aiming for it's hard to advise what's best for you. However, inserting data from a permanent table to a temporary table is something that I do all the time with no problem. The trick for me has always been to define the temp table first and then insert the rows: DROP TABLE TempTable CREATE TABLE TempTable (Col1 Text (10), Col2 Integer) INSERT INTO TempTable (Col1, Col2) + SELECT Name, IdNum FROM PermanentTable WHERE Name... The data from Name will be in Col1 and the IdNum in Col2. That way you can put what you want where you want it and you can easily see what you were doing when you come back to it years later - or the next day, in my case!! For the extra effort now of specifying what you want - and you can easily copy & paste or use the Data Dictionary - you'll thank yourself later. Regards, Alastair. ----- Original Message ----- From: Jim Belisle <mailto:[EMAIL PROTECTED]> To: RBASE-L Mailing List <mailto:[email protected]> Sent: Monday, November 17, 2008 2:22 PM Subject: [RBASE-L] - RE: Inserting into temporary table Buddy, Alastair, Maybe I am going about this the hard way. Would it be easier for me to bring the information up in a form accessing the real table, give component ID to the columns that would get updates (I.E. Quantities, etc.) and just either recalc or refresh the information? I am new to 7.6 and am not familiar with these functions but need to use them if it is easier. Jim ________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Walker, Buddy Sent: Monday, November 17, 2008 7:34 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Inserting into temporary table Jim If in your column list the last item is a comma you will get that error. You have one other choice to add the information to your temporary table with the APPEND if the column names are the same in both tables. APPEND QuoteHeader TO tQuoteHeader WHERE quoteid = .vquoteid The append will ignore the computed columns Buddy From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Jim Belisle Sent: Monday, November 17, 2008 8:15 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Inserting into temporary table Buddy, I did list the columns as you suggested. It seemed to work with me putting two commas next to each other, since one of my computed columns is in the middle of other columns. Also I traced the code again and I now get this error: "Cannot end with a binary operation". Here is my last line before the error: INSERT INTO tquoteheader SELECT (I list the columns here) FROM quoteheader WHERE quoteid = .vquoteid Jim ________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Walker, Buddy Sent: Monday, November 17, 2008 7:08 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Inserting into temporary table Jim The computed columns will cause the problem. You could also do this. It will give you the data from computed columns but they will not be computed in the temp table. PROJECT TEMPORARY tQuoteHeader FROM QuoteHeader USI ALL WHERE quoteid = .vquoteid The other option would be to list all the columns by name excluding the computed columns from INSERT. Buddy From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Jim Belisle Sent: Monday, November 17, 2008 7:42 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Inserting into temporary table I have code in which I want to insert existing information from a table into a temporary table. The temporary table has the exact number of columns as the real table and in the same sequence. I get the error "incorrect number of values for this table". The temporary table does have two computed columns. The code I am using is: INSERT INTO tQuoteHeader SELECT * FROM QuoteHeader WHERE quoteid = .vquoteid I have also used the following with the same result. INSERT INTO tQuoteHeader SELECT all FROM QuoteHeader WHERE quoteid = .vquoteid Is it the computed columns in the temporary table that are causing the problem? Jim ________________________________ No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus Database: 270.9.4/1794 - Release Date: 17/11/2008 08:48
