Marc, Do you have any constraints or rules on the TRAN_HIST table? If you do this could cause an insert to fail.
Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 ________________________________ From: MDRD <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Thu, February 11, 2010 12:15:19 PM Subject: [RBASE-L] - Re: compare rows in 2 tables Hi Why would this happen INSERT + INTO tran_hist (custnum,date_con,tr_date,tr_type,ch_code,+ ch_price,ptest,dig_ch,inscomp,cknum,date_frm,date_to, memo,+ treat_dr,inshold,blddate,tranmod,modf1,modf2,modf3,modf4 ) + SELECT custnum,date_con,tr_date,tr_type,ch_code,ch_price, ptest,+ dig_ch,inscomp,cknum,date_frm,date_to,memo, treat_dr,inshold,+ blddate,tranmod,modf1,modf2,modf3,modf4 FROM tran_daily Inserts 115 rows when there is 116 But is I add a WHERE Custnum Between 1 and 2000 116 rows are inserted. So, with the Where clause all the rows are inserted but without it 1 row gets dropped, very strange Now, I just Packed the Indexes The daily table use to show 116 rows when I did Select count After the Pack Index Select Count now shows 115, so Packing dropped or lost a row of data? Could they have a bad network card or something? The bad thing is they did not have any problems until my last update. Any suggestions? Thanks Marc From: [email protected] Sent: Thursday, February 11, 2010 11:27 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: compare rows in 2 tables I did not really study your statement below, but would have this input.... Normally a good structure setup has a column that is a unique record indentifier. Such as Order Number, PO Number or even a autonum type that might be called RecordNo. If you have that, then finding the "missing data" is easy... Edit ... from tran_Daily where OrderNo* not in + (Select OrderNo from tran_hist) *Orderno is your unique record identifier Not knowing what all your columns are (such as chknum), it would be very difficult to help decipher the issue. However, having many columns to compare, it would take only one for the data to be different and not return any values. If you have a unique column, simply use it. If not and it is not a major implementation, I would add one. I can even be a "behind the scenes column" that is not included in the forms and the user never see it. -Bob ----- Original Message ----- From: "MDRD" <[email protected]> To: "RBASE-L Mailing List" <[email protected]> Sent: Thursday, February 11, 2010 10:51:30 AM GMT -06:00 US/Canada Central Subject: [RBASE-L] - compare rows in 2 tables Hi I am trying to find missing rows of data in a Hist table that is not in our Daily table. We enter rows in the Daily table, then insert them into the Hist when we close the day. I count the # of rows in the Daily table and Hist table and after the Insert we look to see if the Hist table grows by that many rows. For this one user it appears that some of the rows are not making it to the Hist table but trying to search through 200+ rows to compare the 2 tables makes you head spin. This is what I am using but it finds no rows, yet my calculations show we are missing some rows. edit all from tran_Daily + WHERE ( CTXT(custnum) + CTXT(date_con) + + CTXT(tr_date) + ch_code + CTXT(ch_price) + + CTXT(ptest) + cknum + CTXT(date_frm) + + CTXT(date_to) + treat_dr + inshold ) NOT IN + (SELECT ( CTXT(custnum) + CTXT(date_con) + + CTXT(tr_date) + ch_code + CTXT(ch_price) + + CTXT(ptest) + cknum + CTXT(date_frm) + + CTXT(date_to) + treat_dr + inshold) FROM tran_hist) Thanks for any suggestions Marc
