Ed, Assuming that you haven't already done so:
view the table ordered by the date column - or columns in turn - and check each end to make sure that you have valid dates - anything invalid should sort to one end or the other. Be sure that there isn't any invalid - unseen - character. make your DueDate column "not null". Regards, Alastair. ----- Original Message ----- From: Ed Rivkin To: RBASE-L Mailing List Sent: Tuesday, September 15, 2009 3:31 AM Subject: [RBASE-L] - RE: Strange Update Thanks to all for the good ideas and support. Here's what I did this afternoon. a) Eliminated the print report to disk/load to table scenario. The problem still manifested itself with exactly the same # of records added to the Rent Table. b) Switched to another b/u copy of the DB. I generated the 140 records several times and it worked as desired; i.e. 140 records added to the Rent Table. I am sticking with Emmitt's advice and using the new code going forward for this. So it appears that the problem was corruption somewhere along the way. I am still wondering why R:Scope didn't pickup anything. I also discovered that the date errors I was getting reloading the entire Rent table after an unload (Believe this was Karen's input) was likely due to 6 records that didn't have Duedate in them. My user apparently added some special charges without Duedate. George, still not sure what you saw that I am missing relative to date format. I set date in my app startup as mm/dd/yyyy and believe that is what is displayed in the report to disk as well as loaded table. At this point it may be somewhat moot because I replaced the report with a simple program that reads the tenant file for accounts, looks up rent in the rent level file and inserts a record for each tenant in the Rent table. Again...thanks for everyone's assistance/support and any follow-on comments and suggestions are most welcome. Take care, Ed Sep 14, 2009 01:53:59 PM, [email protected] wrote: Paul, Here's some of the code from the .rmd with comments. I've included the code just before the report print to disk, the report print, and load to my Rentemp table and finally to Rentroll itself. BTW, I have been using R:Base Editor when viewing and working with the .rmd file. I assume this will show me any strange/extraneous characters. ----Move all zero balance accounts to Rent History DECLARE c1 CUR FOR SELECT acct FROM balate WHERE baldue = 0 OPEN c1 ---- Get 1st Row FETCH c1 INTO compare INDICATOR i1 WHILE (SQLCODE <> 100) THEN UPDATE rentroll SET rcode = 'U' WHERE acct = .compare AND NOT rcode = 'P' ---- Get next row FETCH c1 INTO compare INDICATOR i1 ENDWHILE DROP CURSOR c1 ---- Move rentroll records to renthist and delete from rentroll UPDATE rentroll SET bcode = 'D' WHERE bcode = 'B' AND rcode = 'U' UPDATE rentroll SET bcode = 'E' WHERE bcode = 'C' AND rcode = 'U' INSERT INTO renthist SELECT duedate acct rent SPECIAL postdate bankdate late + bounce amrec rcode bcode lcode spchg FROM rentroll WHERE rcode = 'U' ---- APPEND rentroll TO spectemp WHERE rcode = 'U' AND SPECIAL > 0 DELETE ROWS FROM rentroll WHERE rcode = 'U' ---- PRINT THE MONTHLY RENTROLL REPORT TO DISK PRINT genrent1 ORDER BY asrt ASC OPTION ETXT |FILENAME + c:\RBTI\RBG76\Galaxy\genrent.gen |SHOW_CANCEL_DIALOG OFF CLEAR ALL VARIABLES ---- LOAD THE RENTEMP TABLE LOAD rentemp FROM genrent.gen USING duedate acct rent APPEND rentemp TO rentroll where rent > 0 ---- rentemp is reduced to a "shell file" and genrent is deleted DELETE ROWS FROM rentemp WHERE duedate EXISTS OR duedate IS NULL ERASE genrent.gen First 5 records from the report 09/05/2009 01JC 534.84 0.00 09/05/2009 02JC 534.84 0.00 09/05/2009 03JC 534.84 0.00 09/05/2009 04JC 534.84 0.00 09/05/2009 05JC 534.84 0.00 First 5 records from the Rentemp table after being loaded as .csv 09/05/2009,73893,"01JC","J01","$534.84","",-0- ,-0- ,"","","","-0- ","-0- ","-0- ","$534.84","-0- ",-0- 09/05/2009,73893,"02JC","J02","$534.84","",-0- ,-0- ,"","","","-0- ","-0- ","-0- ","$534.84","-0- ",-0- 09/05/2009,73893,"03JC","J03","$534.84","",-0- ,-0- ,"","","","-0- ","-0- ","-0- ","$534.84","-0- ",-0- 09/05/2009,73893,"04JC","J04","$534.84","",-0- ,-0- ,"","","","-0- ","-0- ","-0- ","$534.84","-0- ",-0- 09/05/2009,73893,"05JC","J05","$534.84","",-0- ,-0- ,"","","","-0- ","-0- ","-0- ","$534.84","-0- ",-0- First field is Duedate, next is calculated field from Duedate to use for sorting, third field is account, fourth field is calculated field from account to use for sorting, fifth field is rent, calculated balance due is other numeric field displayed. Apparently my computer has been talking to computer. We now have two in the community adding data without our wishes and desires....:-) Take care, Ed Sep 14, 2009 12:59:55 PM, [email protected] wrote: Perhaps post a part of the code. Also if you read this file in RAW format (not notepad but something showing ALL char’s) does it appear to be normal and this is prior to the load/insert etc… Are there extra CRCL’s or odd char’s. Instead of running this export to file with a report, can you output as csv and see if it compares and looks about the same? “When I print the “report” from the R> prompt to disk, 140 records are generated.” Only assuming from your post this is from a report. You may have meant script/code etc… ? Just some thoughts. Sincerely, Paul D. Usually my computer is the only one out there making files and lines on its own. From: [email protected] [mailto:[email protected]] On Behalf Of Ed Rivkin Sent: Monday, September 14, 2009 1:30 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Strange Update Apparently my initial post wasn't clearly written. I apologize for any confusion. The monthly table update of 140 entries is new data. I am generating a rent record for every tenant in my system. Each new record has Rent Due Date, Account, and Rent Amount. We have 30 different rent levels so it isn't just adding a record for each tenant. This is why I write the 140 records as a report to disk and then load the report back into a table. When I print the report from the R> prompt to disk, 140 records are generated. When I load the table from the R> prompt, 140 rows are generated in the table. When I run the .rmd, I am getting over 41,000 records in the loaded table. The records include records from my RentHistory table yet there is nothing happening in the .rmd that moves records from RentHistory back to Current Rent. I appreciate everyone's noodling on this. Any thoughts as to what I am still missing.... Sep 14, 2009 09:03:55 AM, [email protected] wrote: My thought that perhaps the loading table may have error and besides reading every line prior to inserting into his main table. Just to unload it where it is and see what happens on reload. If it fails there also I would tend to believe it would fail everywhere. I also find the process so fast and it takes any questions out of the equation. KISS & JM.02 Sincerely, Paul D. -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of MikeB Sent: Monday, September 14, 2009 7:44 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Strange Update From his original post, it begs the question why is the data printed to disk at all. Is there any sound reason this is not done as an INSERT / SELECT since the data goes to a Master Table from a source table? ----- Original Message ----- From: "Alastair Burr" <[email protected]> To: "RBASE-L Mailing List" <[email protected]> Sent: Monday, September 14, 2009 3:07 AM Subject: [RBASE-L] - RE: Strange Update If you are doing the unload and then an immediate load are you sure that the data has been written to disk and the file closed before the load is attempted? Regards, Alastair. ----- Original Message ----- From: Ed Rivkin To: RBASE-L Mailing List Sent: Monday, September 14, 2009 2:09 AM Subject: [RBASE-L] - RE: Strange Update Anne, Thanks for the suggestion. It was certainly behaving like a corrupted table. I ran the DB through R:Scope and it came through clean on both structure and data. Emmitt, good point but it seems odd that a Disc / Copy / Conn would cause what I am seeing. Since putting in a pause for 30 secs after the Conn (did that after posting my question), it is working fine. As I am updating 3 tables in the .rmd, I just thought it easier to keep everything together rather than 3 unloads.... Ed Sep 13, 2009 06:34:30 AM, [email protected] wrote: Try running R:Scope on the database. I did have something similar happen when updating an operations table. Every time a record was updated a 2nd copy of the record would appear. Found the customer table, which is a feeder table, had corrupted. Reloaded the customer table from a backup copy and all has been running fine. -------- Original Message -------- Subject: [RBASE-L] - Strange Update From: Ed Rivkin <[email protected]> Date: Fri, September 11, 2009 11:04 pm To: [email protected] (RBASE-L Mailing List) I am curious if someone has faced a similar situation. The problem revolves around a .rmd file originally written for my application 20 years ago using r:base 4.0. I update a table monthly with 140 entries; one for each account. To do so, I print a report to disk, load it into a table and append the table to my master table which is the current Rent table. In addition all rows for accounts with a zero balance are moved to the Rent History table and deleted from the Rent Table Prior to the print / load / append the DB is disconnected and the DB is copied so I have a recovery point in case something fails. The only changes from 4.0-4.5 - 7.6 are the screen handling messages. After testing the conversion everything seemed to work fine. Running parallel we are having a strange problem. Many of the current Rent records are duplicated, others tripled and quadrupled and some of the history data is finding it's way back into the Rent file. When I commented out the Disconnect / Copy to Disk / Connect everything worked fine again. Am I hitting some sort of DB corruption issue? Should I put a Pause for 30-45 secs after the connect? Or am I totally missing something else. Thanks as always, Ed ------------------------------------------------------------------------------ No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.94/2367 - Release Date: 09/13/09 05:50:00 ------------------------------------------------------------------------------ No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.96/2369 - Release Date: 09/14/09 05:51:00
