The original code you posted,
UPDATE FECUPLOADTEMP SET FRCODE = FRCODE FROM BIOFILE, FECUPLOADTEMP WHERE FECUPLOADTEMP.IDNO = BIOFILE.IDNO is incorrect. I believe RBase thinks the second FRCODE is also from fecuploadtemp. Try UPDATE FECUPLOADTEMP SET FRCODE = b.FRCODE FROM BIOFILE b, FECUPLOADTEMP f WHERE F.IDNO = B.IDNO Regards, Stephen Markson 416 512 6950 _____ From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: July 15, 2009 1:51 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Help! Program that worked is now broken. That depends on how complex your database is. Always do these tests on a copy of your db. One way to check is to Disc Set multi off Conn dbname Set null -0- Out filename UNLOAD ALL Out scr -- move or rename the db. Now set error messages on and Run filename If you get no errors you are done. The db should be recreated with the same name. That is why you rename or move the db before recreating it Check to see if everything looks normal, correct number of tables, columns, views, and correct number of rows in each table. If you do get errors, you may have to get more elaborate. The things that trip me up most are views with dependencies on views. They don't always get unloaded in the correct order. Hope this helps. Dennis _____ From: [email protected] [mailto:[email protected]] On Behalf Of Paula Stuart Sent: Wednesday, July 15, 2009 12:25 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Help! Program that worked is now broken. Dennis, Packing did not help. The code hasn't changed. The tables are permanent. I just checked and I can do an update WITHIN a table using a where clause that includes the IDNO. It's the lookup updates that don't work. If I rebuild the database, do I unload/reload the tables (unload ALL), or do I need to unload the structures and the data separately? -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: Wednesday, July 15, 2009 12:31 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Help! Program that worked is now broken. Paula, If nothing has changed in your code, and all the tables are permanent, then the trouble is probably in your db. If John's suggestion about packing does not work, you probably have to recreate the db clean using the unload/load (not reload) method. If you do not know how to do this, I'm sure someone here can point you to a description of how to accomplish this. Once the db has been recreated all should work as before. Dennis McGrath _____ From: [email protected] [mailto:[email protected]] On Behalf Of Paula Stuart Sent: Wednesday, July 15, 2009 11:04 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Help! Program that worked is now broken. Hi Dennis, It's a table that is in the database only to run these reports, the structure is permanent but the table contains no rows until you run the program. Then the table is populated with records for the applicable reporting quarter from another table which contains the financial transactions we're reporting. BIOFILE is the primary table containing the primary key IDNO. Other tables use the same IDNO as a Foreign Key linking back to BIOFILE. There are multiple addresses and financial transactions for each individual. This program populates FECUPLOADTEMP with the financial transactions for the quarter from another table, then updates FECUPLOADTEMP with address and other data from BIOFILE and other tables. Once data is loaded into FECUPLOADTEMP properly it's then converted via another table to further refine the format to match the needs of the system we have to upload to. It's basically a four step process to "dumb-down" RBASE to formats the federal government software can read (money and dates are converted to text for example). FECUPLOADTEMP has IDNO as a Foreign Key linking back to BIOFILE. I tried using your syntax with the T1, T2 but that generated a Syntax error and a "no data matches" This is the same syntax and the same program I've used for YEARS to do these look-up updates and it's always worked. Not only does the program not work, but when I try to do this update from the R prompt it doesn't work there either. I thought this was a pretty simple update command. I'd be less insane if the report weren't due today. Paula -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: Wednesday, July 15, 2009 11:16 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Help! Program that worked is now broken. It looks like you are using a temp table. After you load the date in the temp table do you create an index on FECUPLOADTEMP.IDNO ? I have found that this solves a lot of mysteries with temp tables. Also, try this syntax: UPDATE FECUPLOADTEMP SET FRCODE = T1.FRCODE FROM BIOFILE T1, FECUPLOADTEMP T2 WHERE T1..IDNO = T1.IDNO Dennis McGrath _____ From: [email protected] [mailto:[email protected]] On Behalf Of Dan Goldberg Sent: Wednesday, July 15, 2009 10:06 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Help! Program that worked is now broken. Have you tried to see if there is data. select frcode from BIOFILE, FECUPLOADTEMP WHERE FECUPLOADTEMP.IDNO = BIOFILE.IDNO Dan Goldberg _____ From: [email protected] [mailto:[email protected]] On Behalf Of Paula Stuart Sent: Wednesday, July 15, 2009 7:34 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Help! Program that worked is now broken. Yesterday, right before I ran the program. -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Dan Goldberg Sent: Wednesday, July 15, 2009 10:14 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Help! Program that worked is now broken. Just curious, when is the last time you reloaded the database?? Dan Goldberg _____ From: [email protected] [mailto:[email protected]] On Behalf Of Paula Stuart Sent: Wednesday, July 15, 2009 7:10 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Help! Program that worked is now broken. I'm really under the gun here. I have a program that's been running properly for years (RBase 7.1). It is used to extract data from my client's database for quarterly reporting to the government. This involves extracting data from a number of tables and reformatting it in the manner required to import it properly. There are a number of lines of code that are do updates of data from various tables, based on matching IDNO (the primary key for the database). This has always worked before, and now when it runs, none of the data gets updated for those fields. I've checked that the IDNO'S match, the records exist. The data is there. The commands just aren't working. Can someone look at this code and tell me if there's a glitch I'm not seeing? I even tried loading this Database into 7.6 (client isn't up and running on new version yet but will be soon) Same problem. The I extracted one of the command lines (see below) and tried to run it at the R prompt. I got an error message (Syntax problem) and a "no rows exist or satisfy the specified clause" I've checked the tables. There are matching rows with appropriate data. UPDATE FECUPLOADTEMP SET FRCODE = FRCODE FROM BIOFILE, FECUPLOADTEMP WHERE FECUPLOADTEMP.IDNO = BIOFILE.IDNO Checked by AVG - www.avg.com Version: 8.5.387 / Virus Database: 270.13.7/2222 - Release Date: 07/15/09 06:07:00
